asked
How to fix transaction log uncontrollably grow?
I have used `sp_spaceused` to check why one of the database is so big. The results are as follow: ![alt text][1] Then following an article about `sp_spaceused` I have calculated that: • database_size: Database size (data files + log files) = 482.76 GB • unallocated space: Space that is not reserved for use either by data or log files (Space Available) = 166.88 GB • reserved: Space that is reserved for use by data and log files = 198.87 GB • data: Space used by data = 158.46 GB • index_size: Space used by indexes = 39.77 GB • unused: Portion of the reserved space, which is not yet used = 0.63 GB • used: used = data + index_size = 198.23 GB • log space: log space = database_size – (unallocated space + reserved) = 117.01 GB Since I have 168 GB unallocated space and 117 GB transaction log file I run the following `DBCC SHRINKDATABASE` commands on two identical copies of the database and here are the results: ![alt text][2] and if we see the details results below we can see how the space was reduced: **DBCC SHRINKDATABASE(dbname,10)** database_name database_size unallocated space test 392458.81 MB 21346.80 MB reserved data index_size unused 194289672 KB 155724984 KB 38202736 KB 361952 KB **Huge reduce because “unallocated space” narrowed down from 170 GB to 21 GB** **DBCC SHRINKDATABASE (dbname, TRUNCATEONLY)** database_name database_size unallocated space test 380676.50 MB 173146.54 MB reserved data index_size unused 192456664 KB 155625688 KB 36162624 KB 668352 KB **“Unallocated space” increased. Space reduced by: index-size: from 38 GB to 36 GB = 2 GB log file: from 117 GB to 15 GB = 102 GB** ---------- So, I suppose `SHRINKDATABASE` with truncate only option is what I am looking for, since (as my tests shows) if the option is not specified a huge indexes fragmentation occurs. What I am trying to understand is why this transaction log file is so huge. I have read about and I believe it is because the database mode is [FULL][3] and there is replication with [Always ON][4]. Some people says a backup of the LOG file should be done, but I was not able to find information that confirms that. Could you advice? [1]: /storage/temp/1616-1.png [2]: /storage/temp/1618-2.png [3]:
http://sqlstudies.com/2013/08/12/my-transaction-log-is-too-big/ [4]:
http://support.microsoft.com/kb/317375
If you have a database in FULL recovery, you absolutely must run log backups. You must. If you don't the log will just grow and grow and continue growing until it uses all the space on the drive at which point your database frequently will go offline. I [have a blog post][1] that describes this common problem and the only real solution. [1]:
http://www.scarydba.com/2011/01/18/dba-101-why-is-my-log-file-full/
Shrinking your database files is a bad idea. I'd suggest reading the articles referenced in this Brent Ozar post:
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ On a related point, you should be actively managing the space that your database requires. Relying on a finger in the air and autogrowth is a recipe for problems further down the line. This means understanding your likely requirements both now and into the future. How much data do you have now? How much will you have in 1/6/12/24 months time? How transactional is the database, i.e. how much log activity does it/will it generate? What kind of transaction mix are you experiencing? Do you have a lot of activity that prevents log truncation? Once you have some answers to these questions then you can start to make reasoned estimates of how big your database and log files need to be. To get them, try to gather statistics across a representative period and use these to predict the requirements. These Paul Randall articles are worth a read: [Why am I running out of log space?][1] [How big should the log be?][2] [Importance of log management][3] [1]:
http://www.sqlskills.com/blogs/paul/search-engine-qa-1-running-out-of-transaction-log-space/ [2]:
http://sqlmag.com/blog/sizing-your-transaction-log [3]:
http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/
There are times it makes sense to shrink database files. But I agree that it should be done only when it makes sense and not set on any sort of automated schedule or done as a matter of course.
Great Answers so far. If you are concerned about shrinking your transaction log, don't issue the shrinkdatabase command. Use DBCC SHRINKFILE instead. As Grant said, you must perform log backups if you are in full recovery model. As Timothy said, there are times when the transaction log needs to be shrunk. I even have had clients demand that the log file be shrunk every Saturday regardless. You can do it, but managing the transaction log through proper disk size, proper file growth settings and proper backup routines is a FAR better practice. As per the database size that you are showing there, I prefer a different script than the one of sp_spaceused. I like better more complete information. I have also found that sp_spaceused can report inaccuracies sometimes. Here is an alternative to get a bigger picture view of database size, table space used, index space and LOB use. [Table Space][1] [1]:
http://bit.ly/tablespace
17 People are following this question.
