Transaction log file growth

Transaction log expansion may result in the following situations: 

·         A very large transaction log file.

·         Transactions may fail and may start to roll back.

·         Transactions may take a long time to complete.

·         Performance issues may occur. Blocking may occur.

The following are the causes for transaction log growth,

1.    Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.

2.    Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.

3.    Running extremely large transactions like Bulk Insert, Select Into commands.

The following are the proactive measures in order to minimize the unexpected log file growth,

1.    If you do not want point in time recovery of your databases then you can change the recovery model to Simple.

2.    Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

3.    Configure the automatic expansion of transaction log in terms of MB instead of %.

4.    Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.

5.    You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

When your log file has grown enormously you have 2 options to curtail the growth,

1.    Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.

2.    You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.

Hence please try to avoid the 2^nd option I mentioned above. Due to the above fact the truncate_only or no_log option is deprecated in SQL 2008.

You need to schedule a backup of the log periodically throughout the day.  I usually discuss the recovery strategy with the owner of the product and get there expectations of service level.  For internet e-commerce application databases, I run a backup to disk every 15 minutes of the logs that are immediately swept off to tape by Net backup.  For other databases that are "less critical" as defined by the owners, I backup the logs every two hours, and they are managed directly by Net backup.  Other databases are in simple recovery mode.  What happens is when you do a full backup or a log backup, the LSN's in the log get marked as safe, and can be then truncated by SQL and the space is reused.  If you aren't doing the Log backups between full backups, you aren't really benefitting from the Full Recovery Model.  At best you can hope to be able to get the tail log file from disk and use it in recovery to a point in time.  However, if your disks are the cause of a crash, you will only have the full back up on tape (hopefully) that you can recover to.