Alerts with More than E-Mail

4

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.

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

4 thoughts on “Alerts with More than E-Mail

  1. That’s a rather creative use of RAISERROR () WITH LOG.

    I’ve always shied away from WITH LOG in app-level T-SQL because I don’t like my apps to have SYSADMIN or ALTER TRACE permissions. However, for a monitoring agent job, this is perfect. Also, since your throwing the message in the event log, you can capture it with splunk, set an SNMP trap, or deal with it in the other eleventy bazillion ways you can deal with event logs.

  2. […] Alerts with More than E-MailĀ – Some real world in the trenches DBA magic from Mike Hilwig (Blog|Twitter). […]

  3. Salem says:

    Hi,

    Tons of thanks, very good, but is there any way to improve jobs email notification through SQL, like adding job log in attachment. It would be perfect.

  4. Thariq says:

    Wham bam thank you, ma’am, my qunoeists are answered!

Leave a Reply

Your email address will not be published. Required fields are marked *