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.
[SQL]IF EXISTS (
WHERE is_auto_shrink_on = 1 )
DECLARE @v_shrinkcount varchar(3)
DECLARE @v_errormsg varchar(200)
SELECT @v_shrinkcount = CAST(COUNT (name) as VARCHAR)
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