Transaction Log questions

• Margaret Norkett

  SSC Veteran

  Points: 223

  More actions

  January 20, 2005 at 12:02 pm

  #87368

  Our db is 17G and our transaction log is 11G. We back up the transaction log every hour and it only takes between 1 and 2 minutes. We backup the database every day (including the transaction log) and it takes between 4 and 6 minutes to do.

   

  My question(s) are

  Is 11G a reasonable size for the transaction log?

  Should there be anytime we use no_log functionality in our backup jobs?

  How do you determine if there is an open transaction in the log that is keeping it from truncating?

   

  Thanks in advance for your help.

   

  Margaret

  ---

  Thanks and have a NICE day!,

  Margaret
  

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  January 21, 2005 at 6:34 am

  #538498

  You probably want to condiser truncating the log too.

  Regards,
  Matt

• DBAEngg

  Old Hand

  Points: 394

  More actions

  March 11, 2005 at 12:08 pm

  #546700

  See the KB articles for technical info on truncating log file:

  http://support.microsoft.com/kb/q256650/

  or even better yet:

  INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server

  http://support.microsoft.com/kb/317375