Why is mirroring failover suddenly taking so long? RRS feed

  • Question

  • How can I determine what has suddenly started causing the mirroring failover to take so long? We have it manually failing over every ten minutes so that the application developers can test their failover-handling strategy. Last week it was failing over quickly (taking about 10 seconds to completely failover in such a way that the principal and mirror servers switched roles as expected). But within the last four hours the failover time has increased to 4 minutes. I think it is either a huge transaction that was run (although now there is no one running anything against that database) or there is something large in the transaction log? How can I tell what's causing this?

    I know that a large ALTER TABLE transaction was issued at about the time this started happening so that is probably the culprit. But it has long-since rolled back. So why is it still taking up to four minutes to restore/switch?

    engine version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

    Monday, January 24, 2011 7:01 PM


  • Well I answered my own question by experimentation. After I backed up the transaction log it was fine. Do this on the side where the database is the principal.

    BACKUP LOG [Teachers] TO  DISK = N'c:\temp\Teachers.bak' WITH NOFORMAT, INIT,  NAME = N'Teachers-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

    • Marked as answer by MGS66 Monday, January 24, 2011 10:31 PM
    Monday, January 24, 2011 10:31 PM