March 23, 2016 at 12:35 pm
Is there a simple query that i can execute and find when my DB's failed over to secondary? I can look at few other application related stuff to figure it out but was wondering it would be cool to just have a simple query which will tell me when the failover happened?
March 23, 2016 at 11:43 pm
Hi
You may try the script below to query the SQL error logs. There you will see when a failover happened.
declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();
-- Create the temporary table
CREATE TABLE #ErrorLog (logdate datetime
, processinfo varchar(500)
, Message varchar(MAX))
-- Populate the temporary table
INSERT #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';
-- Filter the temporary table
SELECT LogDate, Message FROM #ErrorLog
WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'
ORDER BY logdate DESC
-- Drop the temporary table
DROP TABLE #ErrorLog
Furthermore, below is a script to see the status of your AAG synchronization state, health status and which replica is currently the PRIMARY.
SELECT
GETDATE() AS DateTimeCaptured
,r.replica_server_name
,s.role_desc
,s.connected_state_desc
,d.synchronization_state_desc
,d.database_state_desc
,s.synchronization_health_desc
FROM sys.availability_replicas r INNER JOIN
sys.dm_hadr_availability_replica_states s
ON r.replica_id=s.replica_id INNER JOIN
sys.dm_hadr_database_replica_states d
ON s.replica_id=d.replica_id
ORDER BY s.role_desc, r.replica_server_name DESC
Best Regards,
Ricky
March 24, 2016 at 3:10 pm
Are you asking about a failover cluster or an availability group?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply