locked
Database mirroring suspended SQL Server 2008 R2 RRS feed

  • Question

  •  

    Hi 

    During my index rebuild job, I got this errors: 

     

    2 database in mirror

     

     

    3:15:01

    srv1

    'TCP://1-database.local:port', the remote mirroring partner for database 'database', encountered error 9003, status 15, severity 20. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

    srv2

     

    3:15:0

    Event ID 9003

    The log scan number (348725:54965:4) passed to log scan in database 'database' 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.

     

     

     

    Event ID 1454

    Database mirroring will be suspended. Server instance '' encountered error 9003, state 15, severity 20 when it was acting as a mirroring partner for database 'database'. 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.

     

    I think that this is exactly the sam situation like in this article: http://support.microsoft.com/kb/2403218/en-us?sd=rss&spid=2855

    - with one exception. I have HIGH SAFETY mode on my server!

    Any ideas? 

     

     


    Slaven Sola MCITP DBA,MCT
    Thursday, May 5, 2011 6:28 PM

All replies

  • It looks like your log file on mirroring server corrupted. Why don't you remove mirroring and reconfigure again from scratch. It might worth to check your hardware for any errors?

    http://uk.linkedin.com/in/ramjaddu
    Friday, May 6, 2011 1:05 PM
  • Have you run CHECKDB on the principal? (should be normal maintenance to run CHECKDB but possible it has never been done).  You have corruption in the DB and not know it yet.  Mirroring may have uncovered that for you.  Run CHECKDB and see if any error messages are returned.  Then I would work from there.  I wouldn't reconfigure Mirroring until I knew the principal was stable.
    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 6, 2011 1:17 PM
  •  

    No hardware errors and no corruption (I ran checkdb).

    Resume of mirroirng was finished successfully but I wan't to avoid this in the future. 

     


    Slaven Sola MCITP DBA,MCT
    Friday, May 6, 2011 4:52 PM
  • This still looks like a corruption. In many cases SQL can recover from that and the suspend is only a safety measure. (Like it seems to have been in your case.) There is unfortunately no real way to prevent that. It just happens.
    Wednesday, May 18, 2011 2:21 PM
  • Anyone know if that Cumulative hotfix should be applied to the mirror server, the principal server, or both?

     

    I've got about 20 different principal servers mirroring to the same mirror server, and am only encountering this error from one of the principals.

     

     

    Thursday, July 14, 2011 1:52 PM
  • Hi, I have exactly the same issue - it's always happening to the same database (we have mirroring implemented for a dozen of DBs) once a week during index defrag.

    It was working properly for almost a year on SQL Server 2005. Unfortunately I couldn't find exactly when it started happening, but it's more likely related to SQL Server 2008 R2 upgrade.

    I've ran DBCC CHECKDB few times without any errors. I restore this database on the Mirror server with moving all data files to completely different hard drives. Last week we upgraded firmware to the 'latest and greatest', but nothing changed - still failing.

    In our case it's also affecting the Log Shipping because the log_reuse_wait set to DATABASE MIRRORING in sys.databases and Trans Log starts growing and only disabling database mirroring fix this issue.

    I'm sick tired recreating mirroring every week - any help or suggestions on this problem will be gratefully appreciated. 

     

    Sunday, September 4, 2011 11:15 PM
  • Is it always happening after index defrag/rebuild? I hope you are not rebuilding blindly all indexes.  Without rebuild does it work properly?

    Have a look at Ola's utility http://ola.hallengren.com/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 5, 2011 5:24 AM
  • Is it always happening after index defrag/rebuild? I hope you are not rebuilding blindly all indexes.  Without rebuild does it work properly?

    Have a look at Ola's utility http://ola.hallengren.com/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 5, 2011 5:24 AM
  • Is it always happening after index defrag/rebuild? I hope you are not rebuilding blindly all indexes.  Without rebuild does it work properly?

    Have a look at Ola's utility http://ola.hallengren.com/


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

    This is solid advice here, especially since a complete rebuild of indexes causes massive I/O.  The index rebuild creates a huge transaction log file which it then has to replicate to the mirror.  This issue is only compounded if it a huge database or a VLDB.  Look into Ola's scripts and it'll at least alleviate some of your troubles.

    There is always a way...

    Tuesday, July 12, 2016 9:20 PM
  • Adding -- 

    First identify how your database maintenance is scheduled along with Load,what is the database/index size,what parameters are using while performing reindex,there are cases if high fragmentation,improper maintenance windows time ,large indexes also adds the issues here.

    see are there any other snapshots,verify the space as well.

    Friday, August 12, 2016 9:25 AM
  • Also suggesting you to check- 

    compare the bathces/sec how it is happening is that drastically increasing & does the index size is increasing as well by comparing the few days.

    hope you have space as well in both. 


    Regards, S_NO "_"

    Monday, August 22, 2016 8:14 AM