Why Transaction Log is Growing!!!

Why Transaction Log is Growing!!!

Reasons for Trnsaction Log Growth

Following are the basic reasons for Transaction log growth:

1. When we do bulk insert/update operation.
2. Your database is in Full Recovery model and you are doing Index rebuild operation. Or your database is involved in Mirroring/Log shipping and you are doing Index rebuild operation.
3. If you have Replication implemented in your environment and something is failing in that, you may see transaction log growing unexpectedly.
4. Check the Auto growth value. I have see most of the times high percentage of this causes transaction log growth.

How to Troubleshoot

1. Backup the transaction log if you have enough disk space and your database is not in SIMPLE recovery model.
2. We can user DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the specified database.
3. There could be some transaction running on the server that could be recovered later. Kill those transactions. We can use the command DBCC OPENTRAN.
4. We can try shrinking log file by using command DBCC SHRINKFILE. We may need to user BACKUP LOG with TRUNCATE_ONLY. This is what I use in my QA environment if I face any issues with transaction log growth.

USE <Your DB Name>

GO

CHECKPOINT

GO

BACKUP LOG <Your DB Name> TO <Backup Device Name> WITH NOFORMAT, INIT,  NAME = N'<Your DB Name>-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

DBCC SHRINKFILE(<Name of the log file>, 1)

GO