Sql-server – SQL Server 2008 Mirroring Pauses without warning

mirroringsql-server-2008

I have several databases mirroring with a witness. All 3 machines are local to our network and have generous nics. Monitoring the mirroring, it does not seem like there is a lot of network traffic.

Yet some of the databases' mirroring is pausing without warning. When I restart them, they pause again almost immediately.

What could be the culprit?

EDIT:

Well, now it looks like they are mirroring again…when I went into mirroring manager and tried restarting them, it succeeded now–whereas yesterday it would automatically pause again.

Anyone know why mirrors would just pause? Wouldn't they just failover?

Best Answer

If you query the sys.database_mirroring catalog view you should find out the status of database mirroring from the mirroring_state field. Try this:

select 
    DB_NAME(database_id),
    mirroring_state
from sys.database_mirroring

When it "pauses", what does the mirroring_state show? Here is a legend:

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Synchronous Database Mirroring (High-Safety Mode).

NULL = Database is inaccessible or is not mirrored.

Related Question