mirroring

Question

One of the mirroring DB was stuck in suspended state. I resumed the mirroring session, its going to 'synchronizing state' for a long time and again going to suspended state.

Could someone help with this?

Answer 1

Try partner resume as below

• ALTER ENDPOINT Mirroring STATE=STOPPED
• ALTER ENDPOINT Mirroring STATE=STARTED
• ALTER DATABASE dbname SET PARTNER RESUME

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Thank You!

Have executed the above commands in sequence. Let me wait for sometime & update the results.

Answer 3

The DB is still in synchronizing state.

Answer 4

It is possible that network is unreliable between principal and mirroring so you may see the mirroring state switching back-and-forth between SYNCHRONIZING and SYNCHRONIZED

Also it is very possible that principal generating the transaction log faster than the log can be sent to the mirror

There are details about monitoring Database Mirroring, you can review them and check if the mirroring process can run well during a mirroring session.
http://technet.microsoft.com/en-us/library/ms190030.aspx

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 5

I checked the log on the mirror server & got the below:

Database mirroring will be suspended. Server instance 'xxxxx' encountered error 9003, state 9, severity 20 when it was acting as a mirroring partner for database 'yy'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Error: 1454, Severity: 16, State: 1.

The log scan number (436030:248244:0) passed to log scan in database 'yy' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Error: 9003, Severity: 20, State: 9.

Also I see lot of errors related to I\O.

Answer 6

Also I see a lot of IO errors for the particular DB & can see the mirroring state going back to suspended state even after resuming.

SQL Server has encountered 36449 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\DATA\yy_Data.mdf] in database id 5.  The OS file handle is 0x0000000000001B9C.  The offset of the latest long I/O is: 0x0000b8b8b3e000

Answer 7

Hi SSG92,

 

What is the version of sql server are you using? Please apply the latest cu for it. For more details, please refer to https://support.microsoft.com/en-us/help/4466994/pmm-log-file-grows-large-in-sql-server-db-and-it-will-not-come-online

 

>>SQL Server has encountered 36449 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\DATA\yy_Data.mdf] in database id 5.  The OS file handle is 0x0000000000001B9C.  The offset of the latest long I/O is: 0x0000b8b8b3e000

 

This means that your disk i/o has a lot of problems. In my experience, your mirror server's harden and redo threads are blocked due to i/o problems, so it becomes suspended. I suggest you follow the steps in the link to troubleshoot i/o issue: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/09/troubleshooting-sql-server-io-requests-taking-longer-than-15-seconds-io-stalls-disk-latency/

 

 

Best regards,

Dedmon Dai

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]