Post reply

transaction log backup size spikes

  • December 13, 2013 at 2:08 pm

    #286152

    Hello All!

    I recently moved a production OLTP database from SQL2005 to SQL2012, onto an always-on shared-nothing cluster. The database is 24x7x365, so in order to minimize downtime to just a few seconds, I moved this database through mirroring -- I mirrored from the SQL2005 instance to the new SQL2012 primary, and then set up all the always on stuff after the cutover.

    Everything has gone wonderfully, but I'm seeing some transaction log backup size spikes that I don't understand.

    I take transaction log backups every 15 minutes, and our normal log backup size is between 10 and 20MB every 15 min. Since moving to SQL2012, MOST of the log backups fit that pattern, but at around two separate 1 hour periods of the day, the backups spike to as high as 500+MB per 15-min window. This pattern did not present itself while the DB was running under SQL2005, which is why I am concerned.. it does not line up with any known database maintenance activity.

    The times for the large transaction log backups are also not consistent, though again it seems limited to 1 or 2 1-hour periods during each day.

    If it matters, the database is still running in SQL2005 compat mode..

    Is there any way to dig into these transaction log backups and figure out what is the root cause of their size? Are there new unscheduled database processes under SQL2012 that may be doing something in the background?

    Would appreciate any ideas to diagnose this.. it's not a huge problem, but I'm concerned that it is indicative of something else wrong.

    Thanks,

    Joe

  • December 13, 2013 at 9:08 pm

    #1673452

    Try the semi-documented fn_dblog.

    ApexSQL also has a very cool transaction log reader/recovery tool

    Are you monitoring your mirroring and do you have alerts set to notify you if it gets behind for any reason?

    Have you done a file IO stall and waitstats analysis?

    sp_whoisactive is a wonderful tool as well that could be helpful if you run it while something funky is going on.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply