I am not DBA ,but I need to check on the size of database files . my database log files earlier was around 32 GB but from last few days it starts increasing and finally reached to 70 GB . first I thought it was natural , but soon I realized  that I was wrong  .  For shrinking the size of log file ,firstly I used to determine the % of space occupied in log file  by using .          DBCC SQLPERF(logspace)   Result was :- log size(MB) > 70 and Log Space Used > 94%  with             DBCC LOGINFO   I found more than 700 transactions having Status = '2'  After that I took "log as well as full backup"  and run above query again but still got >700  transactions having Status = '2' detached and reattached , but still no difference in size .  Then I went for Shrinking log files and for testing purpose I gave the size 60GB .  Then run following query again      DBCC SQLPERF(logspace)   and result was :- log size(MB) > 66 and Log Space Used =  99.1258%.  Question is : How can I bring the size of log files near to earlier size i.e. around 32 GB or near by and how to keep check on it so that its size does not change dramatically .  ** Recovery Mode :- FULL   Thanks in advance ....
2459
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:  In SQL Server -  What are some reasons the transaction log grows so large? Why is my log file so big? What are some ways to prevent this problem from occurring? What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?
11117
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming.  The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a general pruning of old data on a database wide level, covering 90% of the tables -- does this constitute an appropriate use case for shrinking the database?  If not, what are the appropriate steps to take to clean house after removing such a high percentage of data from a database?  I can think of two: Rebuild Indexes and Update Stats. What else?
104386
A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's reasonable to assume that environment was not tweaked. The tests are inserting, indexing, searching, deleting. Tool is not known to me.  Given that the windows NTFS block size is 4KB, and SQL write in 64KB chunks - is it safe to assume that a block size of 64k on the SAN is a good choice?  They run SQL Server 2008, perhaps standard.
48042
One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.  Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.  I checked the transactions during the backup and did not find anything strange.  All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)  I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.  I had compared this DB to similar DB size, loading and backup duration. The second DB Log is not growing more than 400GB.  DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.  The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.
13894
Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB.  We've disabled verbose logging, truncated the logging table but now we want to shrink the MDF file to reclaim disk space as the drive the MDF file is on is only around 200GB. I don't have the luxury of increasing its size. Our disk space monitoring tool is alerting me that drive space is less than 8% over and over and over.  Given this is the first instance this has ever happened, we don't expect to ever need that extra 102GB of disk space again. We'd like to reclaim it.   I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size. I've also tried using DBCC SHRINKFILE and DBCC ShrinkDatabase with NoTruncate and TruncateOnly. No matter what I do the file doesn't seem to budge.  One potential issue is that somehow the database initial size is 188GB. Deleting and re-creating the database isn't a very practical option for us so how can I shrink the MDF file and reclaim our disk space?  Running dbcc shrinkfile('My_Data'), I get the following results:  DBID: 5  Field: 1 CurrentSize:24030464 MinimumSize: 128 UsedPages: 17770472 EstimatedPages: 17770472   The database MDF file is still the same size (192,243,712 KB).  The table has a clustered index:  Name:TRACES ObjectID: 394588594 Type: Clustered Index/b-tree   The query:  SELECT     name,     size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files   ...for the database I am trying to shrink, returns:  Name: My_Data AvailableSpaceInMB: 48693.375000 Name: My_Log  AvailableSpaceInMB: 67028.437500   I've also tried shrinking in small chunks using a PowerShell script that shrinks in 20MB increments. That also doesn't change the size of the MDF file.  I've tried dbcc shrinkfile(My_Data) without any other options from SQL Management Studio. It runs for a long time, finishes, and the MDF file is still the same size. Specifying a target size, it runs for a good 30-40 minutes (finishes) and nothing changes file size wise.  I ran this: DBCC SHRINKFILE (N'My_Data' , 139045) and get the following results:  DbId: 5 Field: 1 CurrentSize: 24030464 MinimumSize: 128 UsedPages: 17808744 EstimatedPages: 17808744
79149
Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive.  On monitoring we have found I/O issues on TempDB.  We got an extra drive available - should we move the TempDB Log file to new drive or should we move the second TEMPDb data file on the new drive to help us reduce I/O issue?
117681
I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error:      Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.   There is another table in the database with around 25 million rows.  This database will only be used on a single machine, and it will be designed to mine data that already exists and it under no circumstances will ever grow beyond its current size.    For a situation such as this, what's the best way to tackle this so SQL Server doesn't complain?  Will the solution matter that this DB won't be exposed to multiple users?
120116
Problem:  Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.             What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci  For example a table test is as following     create table test   (   col1 varchar(25),   col2 int,   col3 varchar(3),   col4 char(15),   col5 datetime   );     Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.  Found a some sort of similar solution in MSSQL but need its MySQL version    Any help is much appreciated.
33959
I know how to check space left with:  df -h   and I know to check the space of a folder with:  du -ch /path/to/folder/   But let's say I have a 500 GB HardDisk and 350GB are used:   Wich is the best tool/command to get how the space is distributed ? Should I do du -ch / ? (I think this is not optimal) There is any special tool/app to do this ?    I'd like an app that stores how the space is distributed in my system, wich are the space occuped in each folder...  I'd like to have something like SpaceSniffer (You can check the windows tool ). It is a Windows program that examine your HardDisk and show in a easy &amp; nice IU how the space is distributed on your computer  This kind of software is awesome for that day you check your HardDisk and it seems you've "lost" hundred of GB's and don't know where they are !     Edit   I've tried baobab and runs/works perfectly in my Ubuntu machine, I tested also JDiskReport, here I have to fix some issues but it works in Ubuntu &amp; Windows I assume JDiskReport will work in any &nbsp;OS with Java installed, that's why I choose as correct answer. I've tried this in Ubuntu, Windows 8, Raspbian and CentOS and works in all of them (you have to install Java) I have to say for those lovers of command-line your choice should be ncdu, it's awesome !!!   Baobab IU:
51983
The disk that contains Ubuntu on my computer is 115GB in size.  When the disk was 114.7GBfull.  I started deleting some files here and there to free up around 5GB.  But I noticed the disk was 114GB filled again. I thought it was some cache or swap thingy so didn't give it much thought and went ahead and freed around 40GBby shifting some media files out of the disk.  I have a notification in an hour or so that the disk is full yet again!! 40GB!!! All gone!  So I debugged the problem down to the /var/log/syslog file which was initially definitely less than 20GB. I know this because /var folder was 17GB before I cleared the memory.  /var/log/syslog file is currently 55.9GB!  Could someone be so gracious to explain this anomaly? Is this a bug? Or am I affected with some malware or virus?
58507
how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!
12462
I'm a bit of an Exchange admin noob so I don't know if this is normal, but on our Exchange 2007 server under C:\Program Files\Microsoft\Exchange Server\Mailbox\First Storage Group\ there are 30,000 1MB .log files.  I don't know if those should be there or not, but they keep multiplying like little jackrabbits.  I've so far resisted deleting them as I'm sure something would break, but it's come to the point now where our 80GB disk is completely full up and Exchange has stopped receiving messages.  My best guess is that these are transaction logs and would be useful in rebuilding the database should something go awry, but 30GB of transaction logging seems a bit excessive.  Is there a way to limit it or is that even a good idea?  And of course did I completely miss the point?
63057
When should I use the full recovery model and when should I use the simple recovery model for databases?  I always used the full recovery model because it is the default, but today i encountered this error:     Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction   log for database 'DATABASE NAME' is full. To find out why space in the   log cannot be reused, see the log_reuse_wait_desc column in   sys.databases   The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others.  To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command  alter database myDbName SET recovery simple go dbcc shrinkfile('LOG FILE LOGICAL NAME', 100) go   It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?  EDIT:  Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is  set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak' set @Description = 'Full backup of database ' + @Filename BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT   Is the new recovery model and databaseshrink going to be a conflict with this script?  We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?
32399
I have a Win7 laptop with a 750Gig C: drive.  It came partitioned with 714Gig usable from manufacturer.  I installed programs, music files, etc up to 285 gigs.  As of a few weeks ago it showed 285 Gigs.  Two weeks of house guests later and it shows HD is full.  I deleted some files but it still shows 652 Gigs on this drive while there are only 285 Gigs on drive.  Relevant details:   I am Administrator on laptop and have fair knowledge of what I am doing. I did not restore from backup, restore from mirror, upgrade HD's or anything else that would have touched the partition structure.  Just daily use as imaging machine and web. I have checked partitions under disk administrator - no change, still partitioned with 714Gigs usable. Have looked through computer C drive by hand showing Hidden files and folders - no change. I have used JDisk Report to double check - it shows I have only 285 Gigs on C drive. I triple checked with TreeSize run as Administrator and it also shows 285 Gigs on C drive - yet Windows 7 still shows almost full. I used Windows 7 Utilities to Check for Disk Errors, and Defragged the drive.  No errors shown and no change after Defrag.
73931
I have a Database nearly 1.9Gb Database in size, and MSDE2000 does not allow DBs that exceed 2.0Gb  I need to shrink this DB (and many others like this at various client locations).  I have found and deleted many 100's of 1000's of records which are considered unneeded: these records account for a large percentage of some of the main (largest) tables in the Database. Therefore it's reasonable to assume much space should now be retrievable.  So now I need to shrink the DB to account for the missing records.   I execute DBCC ShrinkDatabase('MyDB')...... No effect. I have tried the various shrink facilities provided in MSSMS.... Still no effect. I have backed up the database and restored it... Still no effect.   Still 1.9Gb  Why?  Whatever procedure I eventually find needs to be replayable on a client machine with access to nothing other than OSql or similar.
81450
It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't.  I am working on a monitoring tool, want to know disk size of each index and data, etc. Eventually I would like to find the right fill factor, and other things etc.  The space used by my function and the old sp_spaceused differs a little bit on the space usage, but not on record count.  Can you see if there is anything missing in my select?  this is the sp_spaceused (then I convert the numbers in MB):  sp_spaceused 'tblBOrderRelationship' go  select 318008/1024.00 AS reserved, 140208/1024.00  AS data, 177048/1024.00 AS index_size, 752/1024.00    AS unused     But when I run my select, code below\picture below, I get slightly different figures.  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  SELECT      schema_name(t.schema_id) as SchemaName,     t.NAME AS TableName,     t.type_desc,     t.is_ms_shipped,     t.is_published,     t.lob_data_space_id,     t.filestream_data_space_id,     t.is_replicated,     t.has_replication_filter,     t.is_merge_published,     t.is_sync_tran_subscribed,     --t.is_filetable,     i.name as indexName,     i.type_desc,     i.is_unique,     i.is_primary_key,     i.is_unique_constraint,     i.fill_factor,     i.is_padded,       sum(p.rows)               OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as RowCounts,     sum(a.total_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as TotalPages,      sum(a.used_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as UsedPages,      sum(a.data_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as DataPages,      (sum(a.total_pages)       OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as TotalSpaceMB,      (sum(a.used_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as UsedSpaceMB,      (sum(a.data_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as DataSpaceMB FROM      sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN      sys.allocation_units a ON p.partition_id = a.container_id WHERE      t.NAME NOT LIKE 'dt%' AND     i.OBJECT_ID &gt; 255  AND T.NAME = 'tblBOrderRelationship'   the figures    the bigger picture, including the index names   Now doing some calculations to check the results:  --================================== -- the figures from sp_spaceused --================================== select 318008/1024.00 AS reserved, 140208/1024.00  AS data, 177048/1024.00 AS index_size, 752/1024.00    AS unused  --================================== -- the figures from my select --================================== select 137+61+56+54 AS reserved,        137 AS data,        61+56+54 AS index_size     It is not so far off, really, apart the fact I did not calculate the unused space!  What can I do to make this accurate?  AFTER CHANGES:  After I replaced 1024 by 1024.00 the results are much more accurate.  I noticed records have been inserted into the table in question, and obviously the stats are not so up to date, but still the results match (under 1 MB difference - which is all right for me)  The new result sets are:  --================================== -- the figures from sp_spaceused --================================== select 318072 /1024.00 AS reserved, 140208 /1024.00 AS data, 177096 /1024.00 AS index_size, 768 /1024.00 AS unused go  --================================== -- the figures from my select --================================== select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved,        137.7578125 as data,        61.7968750+56.4218750+54.6406250 as index_size
80514
Here's my problem  I'm trying to do a cleanup of my hard drive, and I'm constantly seeing this scenario:  (1) Aggregate folder size: I check the folder size viewing properties right clicking the folder  (2) Individual folder/file sizes: I check the individual file/folder sizes belonging to that folder viewing properties on the individual file/folder sizes, and then adding them up (with hidden and system files shown).   These two figures are always different, sometimes by a large margin.   For instance, my C drive now, by method (1), shows a consumed space of 42.4 GB.   However, by method (2), by inspecting the respective files/folders, the total consumed space adds up only to 33.1 GB. This is a 28% discrepancy (over the smaller file size) and I have no idea where the rest of the space is going.   My hard drive is constantly in danger of being full (it's bootcamp on a mac with 50 GB of partitioned space), and there's not much I've got installed in terms of software, nor do I have large media files like movies, downloads, temp files, application temp files, torrents, etc.   Most of it's just consumed by the files in C:/Windows, but last I checked, I don't know enough to know which files to delete, and I've heard that you really can't do anything about the space boggling winsxs and system32.   Can you guys recommend a starting point to free up disk space?  Thanks!!  Baggio.