Saturday, December 29, 2007

Transaction Log Growth

My Transaction Log is Growing!!!

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 in the existing transaction log 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

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
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 SHRINFILE 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.

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

Useful articles
How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511/en-us
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/en-us
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/en-us
How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235/en-us
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
http://support.microsoft.com/kb/317375/en-us

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

1 comment:

Anonymous said...

Thanks mate...Helped me a lot...
Keep it coming!!