I would need some practical suggestions on how to achieve below:

We have quite a few servers involved in database mirroring. As part of various activities and other fixes, we have database failover happening now and then.

What i am generally doing is browsing the sql error log to find the failover details for most of them

Is there a way , we can store or track the failover details for them either locally on server, say saving all that details in a text file or on repository server?


  • Have you seen this TechNet article yet? – John Eisbrener Aug 2 '17 at 17:59
  • @JohnEisbrener, yes i did, but that will show various other alerts. How can i just store the simple failover details – BeginnerDBA Aug 2 '17 at 18:10
  • If you want to tap into the information found in the ERRORLOG, you can run the undocumented system stored procedure sp_readerrorlog. You can pipe the output of this query into a logging table for further review via the INSERT EXECT statement. – John Eisbrener Aug 2 '17 at 18:31
  • @JohnEisbrener FMI, is there a reason to use ERRORLOG rather than something that listens for WMI event (ie extended events/trace)? Always good to learn. – Xingzhou Liu Aug 2 '17 at 18:59
  • 1
    @XingzhouLiu It depends on what you're trying to achieve. In the context of this question, your answer is superior to the sp_readerrorlog approach as it can collect more information depending on the XE definition. However, if I just wanted to be alerted upon a failover, I would actually tap into the SQL Agent Alert engine and look for certain error codes as outlined in the TechNet article I linked in my initial comment/question. The situation will dictate the best approach, hence why there are so many options to accomplish very similar objectives. – John Eisbrener Aug 2 '17 at 22:18

The concept is to create an extended event session listening to the DATABASE_MIRRORING_STATE_CHANGE event. This post covers the mechanics of querying extended event info using TSQL on live server, though haven't used this approach.

Otherwise, you can create the extended events session via SSMS, and configure that session to write the output to a file (xel) which can either be opened by SSMS or by using a system function that can convert the data to xml. The XEL ("event file") itself is binary. easiest way IMO is just to configure some kind of script to copy those files to a central instance, then open and process there.

Fields that DATABASE_MIRRORING_STATE_CHANGE event captures: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/database-mirroring-state-change-event-class.

See https://msdn.microsoft.com/en-us/library/hh213147.aspx for step by step to set up the an extended events session that dumps results to an event file. (needs to be done per instance, though can be scripted). Fairly quick setup to see if it's giving you all the details you need.

You can include the DATABASE_MIRRORING_STATE_CHANGE event class as part of a trace as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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