Checking when Mirroring State Changed

By , May 8, 2012 19:45

This is a script I wrote today to quickly check when a mirrored database changed its status within a particular time frame.

Basically it calls the system stored procedure sp_dbmmonitorresults passing in the variable @dbname. It passes in the paramter @rows_to_return = 9 (basically include everything you’ve got SQL Server) and @update_status = 0 (you don’t need to update your history records SQL Server).
See BOL: sp_dbmmonitorresults

The parameters @start and @end define the timeframe I want to see and the parameter @rowOffset tells the script how many rows I want to see before the status changed.

Now there may be better ways of doing this, but it is a solution that was quickly put togeter and served the purpose adequately.

SET LANGUAGE us_english
GO

IF( OBJECT_ID('tempdb..#MirroringMonitorResults') IS NOT NULL)
	DROP TABLE #MirroringMonitorResults;
GO

DECLARE @dbname sysname = 'DBName',
		@start DATETIME = '2012-05-03 10:00',
		@end DATETIME = '2012-05-03 19:00',
		@rowOffset INT = 10;

CREATE TABLE  #MirroringMonitorResults (
	database_name sysname,
	[ROLE] INT,
	mirroring_state INT,
	witness_status INT,
	log_generation_rate INT,
	unsent_log  INT,
	send_rate  INT,
	unrestored_log  INT,
	recovery_rate  INT,
	transaction_delay  INT,
	transactions_per_sec INT,
	average_delay  INT,
	time_recorded DATETIME,
	time_behind  DATETIME,
	local_time  DATETIME
	);

INSERT INTO #MirroringMonitorResults
	EXECUTE msdb..sp_dbmmonitorresults @dbname, 9, 0;

WITH reduced_data_cte ([ROW], [ROLE], mirroring_state, witness_status, local_time) AS (
	SELECT ROW_NUMBER() OVER (ORDER BY local_time),
	[ROLE],
	mirroring_state,
	witness_status,
	local_time
	FROM #MirroringMonitorResults
	WHERE time_recorded BETWEEN @start AND @end
	),
first_time_disconnected_cte ([ROW]) AS (
	SELECT MIN([ROW])
	FROM reduced_data_cte
	WHERE ( mirroring_state != 4
			OR
			[ROLE] != 1
			)
	)

SELECT	[ROLE] =	CASE [ROLE]
						WHEN 1 THEN 'Principal'
						WHEN 2 THEN 'Mirror'
						ELSE 'Unknown'
					END,
		mirroring_state =	CASE [mirroring_state]
								WHEN 0 THEN 'Suspended'
								WHEN 1 THEN 'Disconnected'
								WHEN 2 THEN 'Synchronizing'
								WHEN 3 THEN 'Pending Failover'
								WHEN 4 THEN 'Synchronized'
								ELSE 'Unknown'
							END,
		witness_status =	CASE witness_status
								WHEN 0 THEN 'Unknown'
								WHEN 1 THEN 'Connected'
								WHEN 2 THEN 'Disconnected'
							END,
		local_time
FROM reduced_data_cte d INNER JOIN first_time_disconnected_cte r
    ON r.[row] - @rowOffset < d.[row]
ORDER BY local_time;

Let me know what you think.

Thanks for reading.

Leave a Reply

*

Panorama Theme by Themocracy