Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am a SQL DBA newbie, and I am trying to get the failover/failback history for one of our mirrored servers. I am able to get the the failover history from event viewer but do not know how to the get the failback history. Your help is greatly appreciated!

share|improve this question
    
Have you tried looking at it in SSMS: technet.microsoft.com/en-us/library/ms365382.aspx –  Shawn Melton Nov 12 '13 at 22:14

1 Answer 1

The following is a script I wrote for displaying the failover and failback history:

SELECT d.name, t3.time, (1 - t3.role) as status
FROM (
    SELECT t1.database_id, t1.time, t1.role 
    FROM 
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time 
            FROM msdb.dbo.dbm_monitor_data dbmd 
        ) as t1
        INNER JOIN 
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time 
            FROM msdb.dbo.dbm_monitor_data dbmd 
        ) t2 ON T1.database_id = T2.database_id and t1.rec = (t2.rec - 1) AND t1.role <> t2.role
    ) t3
    INNER JOIN sys.databases d ON t3.database_id = d.database_id
ORDER BY d.name, t3.time

I believe Role = 0 is PRIMARY.

share|improve this answer
    
Does this require using a monitor instance? –  Jon Seigel Nov 13 '13 at 18:44
    
I don't think so, Jon. I used this in production on a setup where there was a primary, a secondary, and a witness. I could run this query on either the primary or the secondary to obtain a list of failover events. Having said that, I don't presently have a mirror group that I can test this on. –  Max Vernon Nov 13 '13 at 18:54
    
I believe the dbm_monitor_data table might be created by the database mirroring monitor application, and is updated by an Agent job named ‘Database Mirroring Monitor Job’. Again, since I don't have a mirroring setup in front of me I cannot actually verify that. –  Max Vernon Nov 13 '13 at 18:55
    
Whoops, you're right -- I meant witness (had Log Shipping on the brain). I don't have a mirroring environment set up either; I assumed you did from your answer. I was just curious. –  Jon Seigel Nov 13 '13 at 19:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.