SQL Server Home
Database mirroring is paused after restarting the mirror
7/18/2010 1:51:55 PM
User(s) can reproduce this bug
We have more than 10 databases in mirroring mode (configured as asynchronous without witness). two databases are quite big (300GB, 60GB) and the other ones are less than 10GB each.
when we reboot the mirror server (due to windows patches, maintenance, etc), mirroring for the two biggest database stay paused until we resume mirroring manually. the smallest databases get back in sync automatically when the mirror server comes online.
SQL Server 2008 SP1
Windows Server 2008 R2
Operating System Language
Steps to Reproduce
reboot the mirror server
mirroring for two databases is paused after rebooting the mirror server
All the databases back in sync after rebooting the mirror server
to post a comment.
Please enter a comment.
on 8/17/2010 at 10:20 AM
Thanks for providing us with the diagnostics information. It looks like we have fixed a similar bug in DBM for the next major version of SQL Server, but it has not been ported back to the SPs/CUs for SQL Server 2008, because it has not been reported by customers until now.
The issue you are seeing happens when there is a high number of databases on the server. When these databases are starting, they compete for resources. In particular, it may happen that the database mirroring communications thread gets overloaded, in which case DBM message traffic will timeout, causing some of the mirror databases to not start because they think the principal is inaccessible. This is what causes them to enter the suspended state.
In SQL Server 2008 R2 and later, we added a retry logic so that timeouts won't prevent the mirror database from starting.
The workaround is, as you have already discovered yourself, to manually go and unpause mirroring.
If you require a fix for SQL Server 2008, you will have to work with CSS to file a QFE request so that it can be fixed in a subsequent Cummulative Update or a Service Pack.
on 7/19/2010 at 2:19 PM
I did reproduce the problem just rebooting the MIRROR server. this time 4 databases were suspended while 4 were synchronized. I did query sys_database_mirroring view before resuming mirroring.
I have attached another file (db mirror 2.zip) with the new output. It also includes the sql error log for both the principal and the mirror servers.
BTW, I unpause the mirroring session using ALTER DATABASE SET PARTNER RESUME.
on 7/19/2010 at 1:10 PM
Thanks for the quick reply!!
- The principal sever has more 200 databases but just 8 databases in DBM mode. After rebooting the MIRROR server, the state of the DBM session is SUSPENDED but just for 3 databases and it is SYNCHRONIZED for the remaining 5 ones.
- We unpause the mirroring session from SSMS, right-click over each of the PAUSED database -> mirror -> Resume.
I never tried with "ALTER DATABASE SET PARTNER RESUME command" but it should be the the same.
- I have attached an excel file (compressed) with below info
* names of ten the databases in DBM mode (marking the three ones with issues)
* output of sys.database_mirroring view (current). I think it doesn't include the entries of the last time we saw this issue.
* SQL ERROR LOG - PRINCIPAL
* SQL ERROR LOG - MIRROR
BTW, SQL version on both servers is:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
on 7/19/2010 at 10:08 AM
My name is Kal and I am engineer at the Database Mirroring team. Thank you for reporting this issue. In order for us to be able to investigate this issue I would like to ask you a few follow up questions.
- When you say mirroring is paused do you mean that the state of the DBM session is SUSPENDED or you see it being SYNCHRONIZED ? You should be able to see that information by querying the sys.database_mirroring view when the condition you reported occurs. If possible, can you please attach the output from that view to this connect item ?
- How do you unpause the mirroring session - do you issue the ALTER DATABASE SET PARTNER RESUME command ?
- If the state of the DBM session is SUSPENDED, there should be information in the mirror server's ERRORLOG file as to what error did the mirror hit. Would it be possible that you attach the ERRORLOGs from both partners to this connect item ?
Thank you in advance !
to post a workaround.
Please enter a workaround.
db mirror.zip (restricted)
db mirror 2.zip (restricted)
© 2014 Microsoft