SQL Server Transaction Log – Best Practice

Leave a comment (0) Go to comments

Best practices for managing SQL Server Transaction Logs. Steps for better throughput for SQL Server Transaction log.

1. Dedicate a physical disk to your transaction logs. 

2.  Defrag the disks on which your tranaction logs reside to get rid of disk file fragmentation.  (requires downtime)

3.  You only need one transaction log.  Put it on a fast RAID disk (i.e. Raid 1 or faster)

4.  Always back up the 'tail of the log' in a disaster scenario if possible.

5.  Create tranlogs with a reasonable initial size so you don't have to contend with the growth issues on busy systems.  These are the things that dictate size:

  • Type of activity - OLTP or OLAP.  Both should be sized differently
  • Frequency of that activity - the more frequent the changes, the faster the transaction log will grow
  • Recovery Model - the recovery model of the database
  • Frequency of transaction log backups
  • Whether or not replication is used (since the log reader relies on the transaction log)

6.  Don't let autogrowth of the log get out of control.  Percentages of growth can get out of control on busy systems.

7.  Keep file fragmentation internall to the tranlog at a minimum by regular maintenance where you backup the log, shrink it and then reset your file attributes.

Possible Solution if transaction log is full :
  • Backing up the log.
  • Freeing disk space so that the log can automatically grow.
  • Moving the log file to a disk drive with sufficient space.
  • Increasing the size of a log file.
  • Adding a log file on a different disk.
  • Completing or killing a long-running transaction.
EOF - SQL Server Transaction Log – Best Practice, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.