RSS Feed

Alerts with More than E-Mail


July 31, 2012 by Mike Hillwig

One of the things I love about this community is that you’re always being pushed to learn new things. And when I present, someone always asks a question or raises a point that makes me figure out something I didn’t know.

One of the pieces of feedback I got during SQL Saturday 126 in Indianapolis is that sending e-mail isn’t always a practical form of monitoring, and I completely agree. My goal was to show people that they can do this in a pinch.

It seems that in many shops, the Windows Server team has tools to monitor the Windows Event Log. What if we could leverage those tools? I did a little exploring. And once again, leave it to Jonathan Kaheyias to have an answer. I found an MSDN forum post where he mentions using the WITH LOG option on RAISERROR.

I give you a pretty simple solution that will throw an alert into your Windows Event Log when you have autoshrink enabled.

FROM master.sys.databases
WHERE is_auto_shrink_on = 1 )
DECLARE @v_shrinkcount varchar(3)
DECLARE @v_errormsg varchar(200)

SELECT @v_shrinkcount = CAST(COUNT (name) as VARCHAR)
FROM master.sys.databases
WHERE is_auto_shrink_on = 1

SELECT @v_errormsg = ‘This server has ‘ + @v_shrinkcount +
‘ databases with autoshrink enabled.’

RAISERROR (@v_errormsg, 16, 1) WITH LOG