Using SCOM to Monitor SQL Logs

Disclaimer: Due to changes in the MSFT corporate blogging policy, I’m moving all of my content to the following location. Please reference all future content from that location. Thanks.

I learned something new today.  I had a client ask me about how to make SCOM monitor SQL logs.  Generally, the SQL management pack for SCOM is already grabbing key events out of the SQL log and passing them to the SCOM console, but sometimes there are events in the SQL logs that might warrant some visibility that they currently aren’t getting.  A quick search of the issue delivers a lot of really interesting problems and results:

The problems are relatively simple, they are text based logs, and they may not necessarily be in the same location on every SQL server.

This one, for instance, uses a VB script to parse the SCOM attribute and then search for the log. It’s a good solution, and it’s a lot of work. Of course, I’m an idiot who doesn’t know the first thing about VB and reverse engineering it can be somewhat daunting, not to mention time consuming.

One client simply did a text based log monitor.  That works great if the logs are all in the same place, but he found himself creating multiple rules to hit up multiple locations and targeting them to different servers.  Rightfully so, that was turning into an administrative nightmare.

Being the lazy bum that I am, I decided to hit up Kevin Holman to see if he had done anything on this particular issue.  To my surprise, he hadn’t.  He did however send me this Kevin Eckart article that essentially solves this problem.

While it doesn’t always do this, most of the events in the SQL error log are written to the application log. SCOM is very good at capturing items in the application log, and I doubt I need to write a how-to article as this is pretty basic to SCOM administration.  All that needs to be done is to match the time stamps and description of the event, capture the event ID, and then create your rule/monitor.

Now let’s say for a second that this information isn’t available

Using the information from Kevin’s article, it appears that the message ID inside of SQL is tied to the event ID found in the application log.  Note the query and the ‘is_event_logged’ parameter is set to 1:

If by chance the item you want to monitor is not listed, SQL is so kind as to provide us a means to get it written to the application log.

All we need is the message ID (which should be the error number listed in the log).  From there, simply re-run the query:

SELECT * FROM SYS.MESSAGES

where Language_id = 1033 and message_id = 1205

You can see from the query that the value “Is_event_logged” is set to 0.

This also can be changed very easily:

exec sp_altermessage 1205, "WITH_LOG", 'true'

And then re-run the query to verify it changed:

Now in this particular example, SCOM is already monitoring the condition, so no further action would be needed.  If by chance though it isn’t that message_id is the error ID found in the application.  Creating an alert generating monitor or rule at that point is very easy.

Last scenario.  Let’s say you don’t know the message_id.   The SQL error log doesn’t always give you that information.  This search will help you find it in the sys.messages table:

SELECT * FROM SYS.MESSAGES

WHERE LANGUAGE_ID = 1033 and contains(text, 'Sql execution')

At that point, you can run the sp_exec stored procedure again to change it.