I'd just like to know if there is any way to query when the AG group failed over.

eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday? How can i find when the failover took place

is there something specific in the Logs I should be looking for, or is there a tsql script to use?

  • Couple of good talks available on Pass.org. Recommend seeing few from Trayce Jordon. Eg "Finding root cause for unexplained Availability Group failover"
    – jesijesi
    Feb 12 '20 at 21:56

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap 
  • This works and gives a complete history of failovers of the AG (it appears) Note: it didn't work from my workstation - (error the RPC server is unavailable...) but logging into a server in the same dmz gave me access to all other SQL servers in the DMZ.
    – Trubs
    Nov 18 '16 at 4:21
  • 1
    Works great. But as @Trubs pointed out - not working (or working very slow) when running from local pc, but using etsn ListenerName and running command there (without ComputerName parameter ofc) works great. I've added reverse sort (cause I'm that lazy for scrolling) and it's looks like this: Get-WinEvent -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| sort TimeCreated | ft -AutoSize - Aug 5 '19 at 14:26
  • What does id=1641 mean? Sep 17 '19 at 1:43
  • @youcantryreachingme 1641 is the event ID within the Windows event logging system that indicates that a clustered role has moved from one node to another. The id=1641 part causes the Get-WinEvent command to filter for only those events.
    – Doug Deden
    Sep 24 '20 at 15:18

Following lists failover time and direction for each database for all failover events on the server on which this is run, using T-SQL as requested.

-- Script to determine failover times in Availability Group 

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'error_reported'

SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp],
       data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number],
       data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message]
WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480

You can set up a monitoring script (or a 3rd party SQL monitoring solution), to query the various columns of the sys.dm_hadr_availability_replica_states DMV on a regular schedule (e.g. every 30 seconds) and put a timestamp on it of when you've collected the values.

The particular columns of interest related to failover events are:

  • last_connect_error_number
  • last_connect_error_description
  • last_connect_error_timestamp

Additional columns that are worth tracking:

  • role
  • operational_state
  • connected_state

More detailed description for the above columns:

sys.dm_hadr_availability_replica_states (Transact-SQL)

An overview of all DMVs for AlwaysOn Availability Groups with links to more details is available on MSDN:

Monitor Availability Groups (Transact-SQL)

  • can we get for past how many times we have had failover ?
    – kudlatiger
    Jul 20 '16 at 2:45

Although its difficult to find out when the last fail-over occurred, you can get a notification (alert) when the fail-over does occur... (you can then search your email?)

You can create an alert to let you (or your team) know when a fail-over occurred using Error Number 1480


Note: This will send an email for every database in the AG (on each server), which in my case was 12 databases on 2 servers. On the options tab you can set Delay between responses: to something like 1 minute, so that only one email a minute is sent.

  • The reason I marked my own response as the answer was I wanted something within SQL to take care of this and this is a complete answer (imo). And its very useful to know if you've just failed over... If you come to SO looking to find historical failovers, after the fact, I think @mashrur answer is the most complete answer
    – Trubs
    Nov 18 '16 at 4:26
  • 1
    There are useful directions for setting this up here.
    – David
    Jul 26 '17 at 15:25

If the failover is successful, then windows event logs would have an entry

The state of the local availability replica in availability group '' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'

The state of the local availability replica in availability group '' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

This MS KB article has details about the error message and you can use PowerShell script to send you email based on specific events.

Apart from the windows event log, you can look at Cluster Diagnostic Extended Event Log with a format that resembles - ServerName_InstanceName_SQLDIAG_*.xel

  • Thanks Kin, so there is nothing in SQL i can use - I have to use windows logs?
    – Trubs
    Mar 9 '16 at 21:05
  • @trubs You can using XEvents which can fire alerts. But thats something that you have to code yourself and implement. Also, you can use PowerShell script to alert you for particular events. Are you concerned with planned failover or unplanned ?
    – Kin Shah
    Mar 9 '16 at 21:08

This the code I use to determine which server is the Primary node and which one is the secondary node. I use this in every job on my server.

NOTE: this is for a 2-node AOAG. Adjust it as needed to handle multiple-server AOAGs.

declare @PrimaryServer varchar(50),
        @SecondaryServer varchar(50)

set @SecondaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'SECONDARY')

set @PrimaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'PRIMARY')

--If this is the secondary server, do whatever
If @@SERVERNAME = @SecondaryServer 
        --Put your code here for Secondary Server actions
        --Put code here for Primary Server actions

--End If
  • 3
    Welcome Pilot Gal! Thanks for answering, but the question is asking for failover time, not which node is primary/secondary. Jan 30 '18 at 15:28

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.