T-log Backup Size - same as DB

• ovonelmonk

  SSC Veteran

  Points: 237

  More actions

  September 29, 2010 at 12:52 am

  #157227

  Hi,

  I have MSSQL2000 on Windows 2003 box.

  I take Full DB backup at 4am daily. DB backup size is about 12GB.

  And T-Log backup Occurs every 1 day(s), every 1 hour(s) between 12:00:00 AM and 11:59:59 PM.

  At 1am the t-log backup size is 11mb.

  At 3:08am the t-log backup size is 12GB

  At 4:13am the t-log backup size is 2mb

  At 5am the t-log backup size is 1MB and so on..

  I have couple of queries...

  1) Why is there no t-log backup at 2am?

  2) Why us the size so large at 3:08 am?

  If this is not normal, what could be done to reduce the size?

  Thanks in advance

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 29, 2010 at 1:37 am

  #1228792

  Is there an index rebuild job running between 1am and 3am?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Meet George Jetson

  SSChampion

  Points: 11627

  More actions

  September 29, 2010 at 9:31 am

  #1229152

  The 3:08 a.m. time stamp is the time that the 2:00 a.m. Transaction Log backup finished, and thus over ran the 3:00 a.m. scheduled backup.

  More than likely you have some other task running after the 1:00 a.m. Transaction Log backup that is creating massive changes in the database. As Gail suggested/asked, are you running a rebuild/reorg Index at that time, or some other job around 1:00 a.m.?

  Chris Powell

  George: You're kidding.
  Elroy: Nope.
  George: Then lie to me and say you're kidding.