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.
IF EXISTS ( SELECT name FROM master.sys.databases WHERE is_auto_shrink_on = 1 ) BEGIN 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 END