My Perspective: The Top 5 Most Common SQL Server Performance Problems

This post has been moved!

Please visit my new blog here: 

http://blogs.sqlsentry.com/JasonHall/my-perspective-the-top-5-most-common-sql-server-performance-problems/

SQL Agent Alerts & User Defined Errors

Not too long ago I was working with a customer who was having trouble getting a SQL Server Agent alert to fire. At first they thought that Event Manager was misbehaving, but we discovered fairly quickly that the alert itself was never firing at all.

Why Wasn’t The Alert Firing?

With a little more digging I discovered that this was due to the configuration of the alert. The “General” settings were similar to the screenshot below:

The key thing to notice here is the “Severity” setting. Severities 0-10 are considered to be informational or to be non-serious, non-system related errors (http://msdn.microsoft.com/en-us/library/ms164086.aspx). These are not logged to the error log by default. Since alerts of type “SQL Server event alert” are only fired based on events that are logged, this is the reason we’re not seeing the alert fire.

So, How DO We Fire The Alert?

Here we were trying to fire the alert based on an error from a RAISERROR statement. The question from this point is, how exactly *can* we get this alert to fire? The answer is straight forward. We need to get the error to log.
This is easily done using the “WITH LOG” option of the RAISERROR statement. The only thing to keep in mind is that for Event Manager to be able to process the alert, we need to raise the error using a valid error number rather than just the generic user error of 50000 that is used by default. For this reason, we need to create a message prior to raising the error, and in my case I’ll remove it afterward. There is probably no need for the removal, but I’m just a little picky about keeping my system data clean. I’m also not building this for a real system, so away it goes as soon as I’m done with it.

Show Me!

A simple script like the following should do the trick:

-- Add my message so that I can have an error number

EXEC sp_addmessage @msgnum = 50025,

              @severity = 9,

              @msgtext = N'This is a user defined error!';

-- Use the message number that I just created

-- Sev 9 (http://msdn.microsoft.com/en-us/library/ms164086.aspx)

-- State 0 since we're not getting too fancy here.

RAISERROR (50025, 9, 0) WITH LOG;

EXEC sp_dropmessage @msgnum = 50025;

Above we use “sp_addmessage” to add a message for error number 50025. Next I raise the error, with severity 9 and specify that it is to be logged. Finally I just remove the message using “sp_dropmessage”, again because I just want to keep my system data clean.

Note: In SQL Server 2012 you can use the THROW statement to create an error with a custom number without creating a message record as follows:           THROW 50025, N'This is a user defined error!', 0; You should remember though that this will always be severity 16, and can’t be used for alerts since only severities 19 through 25 are logged by default as outlined at http://msdn.microsoft.com/en-us/library/ms189982.aspx and the THROW statement does not provide an option for logging to the error log.

Now I’ll just run this script a few times, and in the SQL Sentry Event Manager calendar I see exactly what we were hoping for:

What Now?

From here I can use the condition and action system in Event Manager to do all sorts of things when this alert is fired. I can run a job, execute a process, email a person or group, execute a TSQL script or just log this to the database.
This is basically how you can get any user defined message to fire an alert that can be monitored by SQL Sentry Event Manager.
Until next time,
-JRH