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