By Richie Lee

Hello!

I’ve been working quite a bit lately with the RedGate SQL Monitor tool, and the more I work with it the more I like it. The analysis tab is very useful for monitoring any changes made and what impact they have on the system in total. But one of the things I really like about SQL Monitor is the ability to create custom metrics. Although there’s something like 33 alerts you can opt in/out of for monitoring, the ability to create your own really adds value in that you don’t have to go down the route of creating SQL Agent jobs to run on each server and to have to email you when an alert could be raised via SQL Monitor.

And so I wrote my first custom metric today. It checks for a change in the recovery model of all the databases on an instance. This is useful as somebody accidentally altered the recovery model of a database to FULL and the log file filled up! I’ve looked into submitting the alert to RedGate, but the submission form on their site is broken at the moment, so I’m posting the T-SQL here. Once it’s submitted, and hopefully approved, I will post a link to the metric here.

I’ve added comments in detail here, but if any questions please get in touch.



--metric that seaches for any change in recovery model
/*	
	start and end time for searching the log file with xp_readerrorlog
	if alert is configured to run at a different frequency than once an hour
	then the start time param should be altered to reflect change
*/
DECLARE @StartTime DATETIME = DATEADD(HOUR, - 1, GETDATE())
DECLARE @EndTime DATETIME = GETDATE()
/*
	some bulk loading/index maintenance jobs alter the recovery model
	these run between midnight and 6am, so we want to ignore these times to avoid false positives 
*/
DECLARE @IgnoreStart DATETIME
DECLARE @IgnoreEnd DATETIME

SET @IgnoreStart = CONVERT(DATE, GETDATE(), 108) + CONVERT(DATETIME, '00:00:00')
SET @IgnoreEnd = CONVERT(DATE, GETDATE(), 108) + CONVERT(DATETIME, '06:00:00')

/*
	insert results of xp_readerrorlog into a temp table
*/
IF OBJECT_ID('tempdb..#ReadErrorLog') IS NOT NULL
	DROP TABLE #ReadErrorLog

CREATE TABLE #ReadErrorLog (
	LogDate DATETIME
	,ProcessInfo NVARCHAR(10)
	,TextOutput NVARCHAR(1024)
	)

INSERT INTO #ReadErrorLog
EXEC xp_readerrorlog 0
	,1
	,N'RECOVERY'
	,N'OPTION'
	,@StartTIme
	,@EndTime

/*
	return only those recovery model changes that were made outside of the 
	changes that are intentional
*/
SELECT COUNT(*)
FROM #ReadErrorLog
WHERE LogDate NOT BETWEEN @IgnoreStart
		AND @IgnoreEnd