Reader Challenge: Silence, Please

Test your SQL Server savvy

[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www .sqlmag.com/resources/main/cfm) to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.]

AND THE WINNERS ARE ...

Congratulations to Mike Spragg of Oasis 400 Software, Hampshire, UK, and John Hutton of IKON Technology Services, Kansas City, Missouri. Mike won first prize of $100 for the best solution to this month's Reader Challenge. John won second prize of $50.

Problem

Sheila likes to be as proactive as possible in her DBA role, but she knows that unexpected situations are a fact in a complex environment. She's content if she can receive notification when system errors occur. She already uses the SQL Server alert functionality and receives email when the Windows NT event log records messages with a severity level between 16 and 25.

Notification is good, but when you get too many messages, you tend to pay less attention to them. Sheila knows that she can safely ignore some messages, for which she knows the error numbers. How can Sheila avoid receiving notification for these error messages?

Solution

This problem has two sides. First, Sheila needs to remove the Always write to Windows NT event log selection for the message she doesn't want to receive. She can remove the message easily in Enterprise Manager by right-clicking her SQL Server, then pulling up the Manage SQL Server Messages dialog box. She searches for the message and clicks Edit. In the Edit dialog box, she clears the Always write to Windows NT event log box.

If that approach doesn't work, she will need to modify the Registry. She will use the key named HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerSQLServerAgent NonAlertableErrors (replace SQLServerAgent with SQLExecutive if you're running SQL Server 6.5). Then she will add the error numbers, in comma-delimited format, for which she doesn't want to receive notification. Suppose she wants to add error number 12345 to the current list. If the Registry key currently looks like this:

1204,4002

she will change it to

1204,4002,12345

Another option is to add specific alerts based on the error numbers that you don't want to receive messages about, and make sure to specify no notification for those alerts. SQLServerAgent first checks for a specific alert for a message before it acts based on severity level. However, because that behavior isn't documented in Books Online (BOL), I prefer the NonAlertableErrors solution.