locked
SQL 2008 Database Mirroring (DBM) RRS feed

  • Question

  • Hello:

    I need your help if you can give me any pointer\direction in DBM. i have issue in production server where i have configured Async DBM. I have tran log backups schedule to run every 15-min on principal but T-Log never got truncated & keep on growing & i was close to 10MB free space left before my database becomes un-operational. so i have to break DBM, backup, shrink the file to reclaim OS space & then re-configured DBM from the scratch. but before doing i did some troubleshooting & didn't gave me any lead beside looking into perfmon for "Log Send Queue KB" which was huge. i didn't see any error on principal event & SQL error logs and same on MIrror Server. i know that my mirror is falling way behind but still didn't understand why it was falling behind. i am doubting either on Network or Storage on Mirror. but again even though if there is latency in storage, it should catch-up but that didn't happen and DBM monitor showed it is Synchronized both can communicate.

    Still i am windup in that puzzle what happened in principal that those transaction log didn't got endup on Mirror & what was holding up. doubting on network but i cannot determine that.

    I am looking for root cause rather than fixing, which is easy to break DBM & start from the scratch.

    Any help is greatly appreciated.

    Thanks

    Monday, August 16, 2010 6:45 AM

Answers

  • Hello:

    Replies are going out of Sync. I am not doing Log shipping over here. As i mentioned, that Async DBM is configured & i have backup plan in place which does FULL backup every night & Tran Log backups every 15mins.

    But eventually i came to know from Paul Randall,  that it is an expected behavior when the storage and network can't keep up with the amount of log being generated on the principal & that was my bottle neck on principal server.

    Thanks

    • Marked as answer by aaditya2000 Monday, August 16, 2010 5:48 PM
    Monday, August 16, 2010 5:47 PM

All replies

  • Database mirroring does not use the transaction log for data transfer (log shipping does)

    As for the mirroring, there is an excellent best practices document available from Microsoft on: http://technet.microsoft.com/en-us/library/cc917681.aspx.

    It was written for SQL 2005, but still valid in 2008.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Monday, August 16, 2010 6:55 AM
  • Tom

    Are  you saying that Log Shipping does transfer the only data and not copy/apply log file on standby server?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 8:33 AM
  • No, I'm saying that database mirroring does not copy over the transaction log.

    From what I understood of the post is that this user thinks that the transaction log is used for database mirroring, which is not the case.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Monday, August 16, 2010 8:51 AM
  • Transaction Log IS used for database mirroring... yes you are right that database mirroring works by copying the transaction log records from the principal database to the mirror,
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 9:10 AM
  • The transaction log records are used for database mirroring, not the transaction log file itself.

    Architecture of database mirroring: http://www.extremeexperts.com/sql/articles/.%5CDBMirroring1.aspx

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Monday, August 16, 2010 9:31 AM
  • >The transaction log records are used for database >mirroring, not the transaction log file itself

    That is why database should be set to FULL recovery mode:-) 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 10:29 AM
  • >The transaction log records are used for database >mirroring, not the transaction log file itself

    That is why database should be set to FULL recovery mode:-) 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 16, 2010 10:29 AM
  • Hello:

    Replies are going out of Sync. I am not doing Log shipping over here. As i mentioned, that Async DBM is configured & i have backup plan in place which does FULL backup every night & Tran Log backups every 15mins.

    But eventually i came to know from Paul Randall,  that it is an expected behavior when the storage and network can't keep up with the amount of log being generated on the principal & that was my bottle neck on principal server.

    Thanks

    • Marked as answer by aaditya2000 Monday, August 16, 2010 5:48 PM
    Monday, August 16, 2010 5:47 PM