none
mirroring RRS feed

  • 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?

    • Moved by Tom Phillips Monday, October 7, 2019 12:12 PM Mirroring question
    Monday, October 7, 2019 10:05 AM

Answers

All replies

  • 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

    Monday, October 7, 2019 10:39 AM
  • Thank You!

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

    Monday, October 7, 2019 11:10 AM
  • The DB is still in synchronizing state.
    Monday, October 7, 2019 1:48 PM
  • 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



    Monday, October 7, 2019 1:57 PM
  • 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.

    Monday, October 7, 2019 3:32 PM
  • 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

    Monday, October 7, 2019 4:17 PM
  • 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 MSDNFSF@microsoft.com

    • Marked as answer by SSG92 Thursday, October 10, 2019 12:08 PM
    Tuesday, October 8, 2019 7:52 AM