Alerting on SQL Server events in SQL Server 2005

In SQL Server 2000 I have several Alerts set to respond to SQL Server events. One in particular is 3738 which lets me know a database is deleted. In SQL Server 2005, this event is not set to log, and therefore I am not getting the alert to fire. I tried to run sp_altermessage, but it will not let you modify system messages. I configured a WMI alert to trigger off of SELECT * FROM DROP_DATABASE, and this tells me that a database has been deleted. However, the error based alert in SQL Server 2000 would also tell me which database was deleted, and the WMI alert does not. This information is important for determining the priority of my response. Does anyone know how I can get an alert configured in SQL Server 2005 which will also include the name of the database being deleted?

Note, I have similar issues with failed login and about a dozen other alerts. WMI does not appear to provide the detail that the error based alerting used to. But I am pretty new to WMI, so maybe there is something that can be done to get WMI to include the desired level of detail.

Thanks

John

I now have failed login alerts working using error numbers. (I fat fingered the error number.) But I continue to have trouble getting SQL Server Agent Alerts to fire for errors associated with database creation and deletion. It appears to be due to errors not being set to log, and no way to change that. Any ideas on how I can get meaningful information about these events?

Thanks

John

|||

Hey John. The properties exposed by each given WMI event type/group should be able to give you everything you need. For example, in the DROP_DATABASE event type will provide you the DatabaseName event property that should give you the name of the database that was dropped. To see a list of all the properties exposed by the DROP_DATABASE event type, see the following link:

http://msdn2.microsoft.com/en-us/library/ms179622(SQL.90).aspx

Within the SQL Server agent job you create to respond to this event, you can create T-SQL similar to the following to get the name of the database that was dropped:

select '$(WMI(DatabaseName))'

Within the given SQL Agent job you configure, you should then be able to do whatever you like with the DatabaseName, including setting it to a local variable or inserting into a table, etc.

NOTE: You may have to enable a registry key in order to allow for run-time replacement of tokens within the SQL Agent. If you receive an error to the effect of "reason: Variable WMI(DatabaseName) not found", open your registry editor and navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent hive and enable the "AlertReplaceRuntimeTokens" key. Once you do that, restart your service and you should be good to go.

HTH

|||

Chad,

Thanks for the answer.For clarification, no matter what you put in the query in the definition of the SQL Server Agent Alert on a WMI event that information will not be included in the email response?I have currently a query in the WMI query for the alert which should return the information I want, but still the information is not included in the email. So instead we need to create a SQL Server Agent job that queries the WMI data we want to see and email it to us, and then have the SQL Server Agent alert call that job as the response.Just want to make sure I am following what needs to be done.

What was so bad about allowing sp_altermessage change the logging property of system messages?It seems that it could have coded to not allow the changing of any property except logging for system messages, and then none of this would be an issue.

Sorry, just a little frustration coming out.But I am curious to know if I am understanding what needs to be done.

Thanks

John

|||

That's correct...to get the information from the WMI query, you'll need to create a job that then formats and sends an email based on the information you get from the WMI query.

I can't really speak to why you shouldn't be able to allow logging of a system message, that would have been a decision by the project team, though I'll be happy to put in a request to have it reviewed and possibly changed back...though I can't ensure anything will actually come of it.

HTH,

|||

Chad,

Thanks for all your help. That would be great if you could put in a request. I think that would make life easier on a lot of people. As for now, I'll work on creating the jobs and alerts.

Thanks

John