The transaction log growth issue… What do we do?? A few things NOT TO do when this happens:
1. Delete the LDF file of the database
2. Create a new LDF file if there is no space on the disk drives and if the SQL Server ERRORLOG is reporting 900* messages. (Personal opinion.. Seen a lot of situations where this is has gone horribly wrong on production environments)
3. Take the database offline
4. Restart the SQL Server service
Believe me the above options are exercised some times on production scenarios.
Now that the above steps are avoided, what is to be done. Simple set of steps can be done to shrink the T-LOG of the database in question using the steps mentioned in the KB Article below. My troubleshooting steps involve the following:
1. Check the current SQL Server ERRORLOG for any 900* error messages related to the T-LOG
2. If you have enough disk space and the database is not in SIMPLE recovery, make a backup of the transaction log so that the you can most of the current active virtual log files
3. Use DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the database in question
4. If it’s SQL Server 2005, find out the log reuse value in the sys.databases catalog view output. This will tell you why a database transaction log is not being reused.
5. Find out if there are any open transactions using DBCC OPENTRAN command. KILL the transaction if you can ascertain that this open transaction can be redone or recovered later.
6. Try shrinking the T-LOG using DBCC SHRINKFILE or if it is in SIMPLE recovery, try using BACKUP LOG WITH TRUNCATE_ONLY command. But be advised that this truncate command will truncate your transaction log. OR You can even try firing a CHECKPOINT into the database and then trying to truncate the transaction log. This works for only SQL Server 2000 instances.
** I know a lot of people frown on the shrink and truncate options but when you are strapped for time, this might be your ONLY option.
If you are truncating the log, then you need to take a Full Backup and then continue your log backups (if FULL/BULK LOGGED recovery model is used) since truncation breaks your log chain. You should always try to find out the RCA for the issue (if possible and if you get the chance) because you don’t want the same (as Bruce Willis says in Die Hard ) thing happen to you twice!!
Another important thing is to make sure that your autogrowth considerations are taken into account. The following article should help you decide what needs to done in deciding the autogrowth of a database file: http://support.microsoft.com/kb/315512/en-us
For more information, see the following topics in SQL Server Books Online:
• Shrinking the transaction log
• DBCC SHRINKFILE (Transact-SQL)
• Truncating the transaction log
How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
INF: How to Shrink the SQL Server 7.0 Transaction Log
How to stop the transaction log of a SQL Server database from growing unexpectedly
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
Common Reasons for T-LOG growth
1. Wrong autogrowth values (Mostly high percentage values)
2. Index rebuild operations for database done infrequently with database in full recovery model or with log shipping/mirroring enabled
3. Something failing in your replication topology
4. BULK insert/update operations
5. Long running transactions
In case, the above mentioned steps and articles do not help you, please call Microsoft SQL Support.
Excellent man – I had this blog in my list to read and here it is – great one!
Thanks Pinal. 🙂
Excellent explaination Amit. Wish will continue the same which is very helpful for people like me.