locked
logs for 'Operating mode' change in mirroring RRS feed

  • Question

  • Logs for 'Operating mode' change in mirroring

    Hi Everyone,

    Is there any log,that shows when the Operating mode of a database involved in mirroring(SQL SERVER 2008 R2) has been changed from asynchronous to synchrous(or vise versa).

    Regards,
    Aspet


    A.G

    Tuesday, September 17, 2013 11:49 PM

Answers

  • That change did not happen automatically.  Someone with sysadmin rights manually changed the mode of the mirroring.

    You might see this change in the SQL Server error log, however I don't think so.  This would be a change to the mirroring configuration and is normally not logged.

    • Proposed as answer by Rama Udaya Monday, September 23, 2013 5:43 PM
    • Marked as answer by Fanny Liu Wednesday, October 2, 2013 6:34 AM
    Friday, September 20, 2013 3:12 PM

All replies

  • Logs for 'Operating mode' change in mirroring

    Hi Everyone,

    Is there any log,that shows when the Operating mode of a database involved in mirroring(SQL SERVER 2008 R2) has been changed from asynchronous to synchrous(or vise versa).

    Regards,
    Aspet


    A.G

    Hello,

    Since changing mode requires running Alter database command on Principal server ,I assume this will be logged in SQL server errorlog.You  have to run below command on principal to change it to Asynchronous

    Alter database db_name set partner safety off
    Please run SP_readerrorlog and check in errorlog.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Wednesday, September 18, 2013 5:09 AM
  • http://technet.microsoft.com/en-us/library/dd207006.aspx

    To monitor state changes in a database mirroring session, use the Database Mirroring State Change event class. For more information, see Database Mirroring State Change Event Class.


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, September 18, 2013 5:59 AM
  • Hi Thanks for your reply,

    I already had check (and checked again), but nothing is being logged in errorlog.


    A.G

    Wednesday, September 18, 2013 6:01 PM
  • Thanks for your reply.

    But the event show the state has changed from synchroninizing to synchronized,It does not show the state has changed from Synchronous to asynchronous.


    A.G

    Wednesday, September 18, 2013 6:34 PM
  • The operating mode does not change automatically from "Synchronous to asynchronous".  This is configured  by the SAFETY option when you create the mirroring connection.

    What exactly is your issue?

    Wednesday, September 18, 2013 6:38 PM
  • All of our mirrorings are working on 'high Performance'(Aysnch) operating mode,all of sudden we noticed that one of the servers has 'High safty'(synch) operating mode,We want to check when this change has happened.


    A.G

    Thursday, September 19, 2013 11:46 PM
  • Try looking into trace log files

    DECLARE @filename VARCHAR(100), @filenum int
    SELECT @filename = CAST(value AS VARCHAR(100))
    FROM fn_trace_getinfo(DEFAULT)
    WHERE property = 2
      AND traceid = 1
      AND value IS NOT NULL

    SELECT @filename
    SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'
    SELECT @filename
    SELECT gt.EventClass, 
           te.Name AS EventName,  
           gt.TEXTData, 
           gt.NTUserName, 
           gt.NTDomainName, 
           gt.HostName, 
           gt.ApplicationName, 
           gt.LoginName, 
           gt.SPID, 
           gt.StartTime, 
           gt.EndTime, 
           gt.ObjectName, 
           gt.DatabaseName, 
           gt.FileName 
    FROM [fn_trace_gettable](@fileName, DEFAULT) gt 
    JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
    WHERE EventClass = 116 
      AND TEXTData LIKE '%Alter database%' 
    ORDER BY StartTime; 


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, September 20, 2013 3:25 AM
  • That change did not happen automatically.  Someone with sysadmin rights manually changed the mode of the mirroring.

    You might see this change in the SQL Server error log, however I don't think so.  This would be a change to the mirroring configuration and is normally not logged.

    • Proposed as answer by Rama Udaya Monday, September 23, 2013 5:43 PM
    • Marked as answer by Fanny Liu Wednesday, October 2, 2013 6:34 AM
    Friday, September 20, 2013 3:12 PM
  • Hello Uri

    I assume this (Database Mirroring State Change event class) works only if a trace was started...right? I found a sys view called [master].[sys].[dm_db_mirroring_past_actions] and apparently log UI actions but does not have date. :-(


    Pedro Morales

    Friday, January 17, 2014 2:02 PM
  • If you have the "Database Mirroring Monitor Job" turned on, then it gets logged.  However, the default is to only keep 7 days.

    http://technet.microsoft.com/en-us/library/ms190030.aspx

    Tuesday, January 21, 2014 1:08 PM