Post reply

SQL backups - Logs Causing transactions to wait

  • July 4, 2011 at 8:44 am

    #243759

    Hi we on 2005 and 2008 and i am trying to determine if our backups logs are causing problems for other transactions to commit at the same time with the transaction logs running. Everytime our users complain it is slow I see that the backups logs are running. We run this for every database on the SQL Server.

    Is there a way to determine that backups are taking the entire processor when they run and are a priority before any normal insert/update transaction occurs?

    I know in SQL 2008 you can use resource governor and this you can say 20% cpu is for Backups.

    Any thoughts? Are transactions held up whilst backups finish? Any thing I can capture waits at this time to identify it?

    Thanks

  • July 4, 2011 at 8:50 am

    #1347902

    Run this query and post the results here.

    http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

  • July 4, 2011 at 8:58 am

    #1347910

    Log backups don't block transactions.

    You could be hitting the limit of the IO that the drive can handle and the additional from the log backup is just pushing over the limit.

    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

  • July 10, 2011 at 7:24 am

    #1350825

    Thanks, I will start to investigate the IO and waits. San results look good no issues there but could be the SQL Server IO pushing to the SAN.

Viewing 4 posts - 1 through 3 (of 3 total)

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