Availability Group Fail Over date/time – TSQL

I have been using this quick script to verify AG Status Change details from error log.

--


DECLARE @EndDate DATETIME = GETDATE() 
DECLARE @StartDate DATETIME = dateadd(dd,-31,GETDATE() )

exec xp_readerrorlog 0						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 1						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 2						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 3						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 4						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

exec xp_readerrorlog 5						-- 0=current SQL Server log
						,1						-- 1=SQL Server log, 2=SQL Agent log
						,N'The state of the local availability replica in availability group'					-- Search string
						,null					-- second search string
						,@StartDate			-- start date
						,@EndDate	-- end date
						,N'DESC'				-- order the logs by date

--
Advertisements
This entry was posted in High Availability, SQL, SQL Query, SQL Server, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s