Monday, 14 April 2014

SQL SERVER – How to Stop Growing Log File Size (Performance Tuning)

Use following 8 methods to solve the log file growing issue-

Tips 1) Suppose you database is inserting only 100 row per hour and you are taking the T-log backup every 30 minutes than you will see Log file will automatically increase. So that minimize the frequently T-Log backup.

Tips 2) Using Simple Recovery model you can stop the size of growing log file.

Note - Some time most of the DBA Truncating the T-Log but In this situation you can not recover the Point In Time recovery because it's remove the LOG SEQUENCE NUMBER (LSN) of T-Log file.

Tips 3) If you are not worry to suspect your database then you can truncate the T-Log file

Tips 4) Using Shrink Database you can also stop growing log file size. but it depend on the situation. genrally don't perform the Shrink Operation on the database or log file.

Tips 5) Add new file group 

Tips 6) Add new Disk

Tips 7) Increase Auto Growth of the database

Tips 8) Break/Split long running query logic into chunk.( for SQL Developer)

If you have another tips then please comment below

No comments:

Post a Comment