5

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.

1
  • Just so you know, by running Rebuild Index then after that Update Statistic for the same indexes, you are not doing yourself a favor. Rebuild Index updates stats with a FULLSCAN but Update Statistic uses a SAMPLE option. You are actually making your stats worse by doing that!
    – DenisT
    Mar 7, 2014 at 16:27

3 Answers 3

8

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.

3
  • 1- You might be able to control the growth better by backing up the transaction log more frequently and setup transaction log backups to run every 15 minutes, 24 x 7, or even more frequently if there is high activity.


  • 2-Under the full recovery model, all bulk operations are fully logged. However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations. Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.
  • 3-You would be better helped using a separate rebuild process like scripts. These allow much finer control of how your run rebuilds/reorgs. Here is a link to help you with scripts that you mat need!!
3

increaseing the log size duing the opeartion like REBUILD /REORGANIZE / Update-STATS is normal depending on how much actual data size and how much it grows. What we can reduce is unnecessary disk uasage due to the incorrect implemnation of maintanance plan.

Firts Read the folowing MSDN BOL information sincerely.

Choosing a Recovery Model for Index Operations

From the table you will notice that even if you have database in SIMPLE recovery model, REORGANIZE operation is Fully Logged. but Rebuild is Minimally logged.

Reorganizing and Rebuilding Indexes

From above link you will find that Rebuild Clustered Index without disableing NONCLUSTERED index can have huge impact on DISK USAGE. Also the order of REBUIDL/REORGANIZE operation for CLUSTERED and NONCLUSTERED index is important. All NONCLUSTERED OPERATION should be followed by CLUSTERED INDEX operations.

Also notice the infomation Where it explanins the REBULD of large index in two phases. second phase of that opeartion runs in background and does not afect db performance but the down side is disk space will be remain used untill that phase is finished.

Now what are the logical steps you can take?

0) Make sure the Database is in right Recovery Model. if db has "FULL RECOVERY" mode, then you must have proper transectional log back up process as well. if not then either "FULL Recovery" is not really needed or you should revisite the LOG Backup process currently implemented.

1) check the maintanance plan and make sure you are doing REBUILD OR REORGANIZE as based on the right conditions. (for example fragmatation percentage.) in some cases you are sure that you want to rebuild index for specific table thats fine.

2) check if opertaion on NONCLUSTERED indexes are followed by OPERATION on CLUSTERED INDEX. if not update the plan accordingly.

3) you are using STANDERED EDITION so all operation are happening OFFLINE. but also check if SORT_IN_TEMPDB option is being used in REBUILD/REORGANIZE.

4) if answer of (3) is NO: "Whihc is most likely because you actual DB log size is increases and you haven't mentioned anything about tempdb" then consider using that option after looking at checks I mentioned in point (5).

5) if answer of (3) is YES: "which is unlikely" yet if thats the case, that means you also need to check Tempdb Configuration is doen proerly or not. like,

    Does it have more than one LDF and MDF file? if no have atlease 2 for each.

    Are those files are set to increase at right amount instead of default 10mb?       
    if default then start with the 1024MB increament. gradually you should find the 
    sweet spot.

    Are those Files are ristricted to some size? if so check the disk size where the 
    files are and set accordingly.

    Even If your Tempdb is not located on Seperate Disk, from the disk Actual DB is    
    on, making sure above checks is good practice amd sure help you.

6) As Shrink log operation is not devil if done moderatly same way REBUILD/REORGANIZE is not angel when you overdo it. you shold check if REBUILD/REORGANIZE on whole DB is really neccessary? may be weekly or every 3 day works better.

I am happy to go over next steps/suggestions about Statistics once you try out above and get some results. I will not expect that above steps megically solves your concern compeletly but yes, those will definetly improve the situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.