Sql-server – Encountered unexpected growth Transaction log file size

sql serversql-server-2008-r2transaction-log

We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance Plan, Database transaction log file size increased abnormally. For suppose, if Primary Data file is 7 GB, the transaction log file grows to 55 GB.

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

The Other solution ever one recommended is Transaction log backup after Full Backup. We did same and observed no change in file sizes it shows same size as before Primary file is 7 GB and Transaction log file is 55 GB.

Please suggest me best way to reduce log file size without any effect on Performance.

Best Answer

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance)

That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign.

My first recommendation is to use a smart index maintenance plan. Overactive rebuild is expensive, harmfull and completely unnecessary. Many swear by Ola Hallengren's index maintenance scripts.

You must also look into leveraging minimal logging. Index maintenance are a prime candidate for minimally logged operations, but you must use enable them by using the bulk-logged recovery model for your database. See Operations That Can Be Minimally Logged:

If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online.

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.

Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group

Related Question