SQL Server Performance

mirroring suspended

Discussion in 'SQL Server 2005 Database Mirroring' started by contactjimmyus, May 28, 2008.

  1. I came to know that one of the server that was mirrored was suspended .I tried to resume mirroring by clicking the resume button and nothing happens on the screen .The db still in suspended mode .HOW TO FIND THE REASON FOR THE FAILURE .Is is due to transaction logs ?The logs are growing now and we have an hourly transaction log back up
    Please suggest so that I can resume mirroring .
    Thanks
  2. satya Moderator

    Check the database mirroring monitor and SQL SErver error log on Principal & mirror servers to see why it is failing.
  3. the mirroring history has status from 21st may to till date .But all shows as suspended Do i need to redo the mirroring since I dont have full transaction log from 25st.How to script out the current setting and redo it ,if needed
  4. satya Moderator

    FIrst of all check the issue root cause from the Error logs.
  5. The principal server has space issue and it was logged in the sql server log .The error desc from mirror server was (a45-u1 is the principal server and b45-x1 is the mirror server) the error was
    The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000002000 in file 'E:TLOGSRRHTLOGRRH_1.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).
    This error can be caused by many factors; for more information, see SQL Server Books Online.
    Operating system error 21(The device is not ready.) on file "E:TLogsRRMTLogRRM_1.ldf" during ReadFileHdr.
    While acting as a mirroring partner for database 'RRI', server instance 'A45-U1' encountered error 5159, status 3, severity 25. Database mirroring will be suspended. Try to resolve the error and resume mirroring.
  6. Now the space issue is tempororily resolved.Mirroring was suspended for few days and I dont have all the transaction files .I hace recent full and tranaction files .So how to resume mirroring .
    can i remove the mirroring and start a full back up ,restore at mirror sercer and set up mirroring again ? if so how to script the current mirroring setting ?
    Thanks
  7. satya Moderator

    Again I would like to refer that not to post duplicate threads for same problem, as http://sql-server-performance.com/Community/forums/t/26917.aspx fyi.
    COming to the problem, have you performed the failover or do you have any changes to principal and mirror instances. In order to continue the mirroring you have to take full lbackup and restore to the server which you deemed to be Mirror, then start mirroring as you configured earlier.
    As per the updated books online you could event build up a script well before to configure DB Mirroring, http://blogs.msdn.com/lcris/archive/2005/09/14/466268.aspx fyi in thsi case. Also see this http://www.sqlserver-qa.net/SSQA-Effective Usage of SQL Server 2005 Database Mirroring_show.ppt about using DB Mirroring in a flexible manner.
  8. Ihave removed the mirroring for one DB from the primary server and taken backup of the db and transferd to the mirrored server .The mirrored db is in mirror disconnected in recovery.Iam not able to change the status of the db to online .I tried RESTORE
    DATABASE RRI WITH noRECOVERY;ALTER DATABASE RRI SET PARTNER OFF
    But the message says that the database is in mirroring and so canot proceed
    is there a way to change the status of the db in system tables ? ( eg sys.database_mirroring)
    Thanks
  9. satya Moderator

    What is the error you are getting?
  10. The mirror db is in mirror disconnected in recovery
    I ranALTER
    DATABASE RRI SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    The output was Msg 1404, Level 16, State 5, Line 1
    The command failed because the database mirror is busy. Reissue the command later.
    The principal server db is removed from mirroring
  11. Now I need to remove the db from the mirror server and recreate the mirror .Does there a way to alter the system tables and make the db online.
  12. satya Moderator

    If you have stopped the Mirroring on principal server, then drop the database on mirror server in order to redefine the DB mirroring flow.
    You have to ensure the SQL server services have the same name and password as they are in workgroup; also check the log on mirror server to ensure the REDO queue is completed.
  13. The total scene is : The mirroring was suspended due to space issue and I dont have all the log files .I only have current full back up and transaction files .
    I have already removed the mirroring from the primary server and ran restore log RRH with recovery on the morror server .IT make the mirror db into mirrored disconnected in recovery mode .I am not able to delete or detach the db .The error I got when detaching or deleting was the database was enabled for mirroring so remove the mirroring before deleting it .
    suggest How I can remove the db in mirror server online so that I can delete ,restore from the primary server and set up again for mirroring .
    Thanks
  14. Can any one suggest the resolution .......
    Your responce is appreciated

Share This Page