question

Sharma avatar image
Sharma asked

How to get SQL Server available alerts infomation ?

Hi, Am looking SQL query to get all available server alerts information including- db_name, and all related information about alerts
sql-server-2008alerts
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Did you mean [sysalerts][1] table in msdb database? [1]: http://msdn.microsoft.com/en-us/library/ms187814.aspx
2 Likes 2 ·

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
Alerts information is stored in the msdb database. Here is a sample query to get a little information about alerts on your system: SELECT SA.name AlertName, SC.name AlertCategory, SJ.name JobName, SO.name OperatorName, SO.email_address OperatorEmailAddress FROM dbo.sysalerts AS SA LEFT JOIN dbo.sysjobs AS SJ ON SA.job_id = SJ.job_id LEFT JOIN dbo.sysnotifications AS SN ON SN.alert_id = SA.id LEFT JOIN dbo.sysoperators AS SO ON SO.id = SN.operator_id LEFT JOIN dbo.syscategories AS SC ON SC.category_id = SA.category_id Be aware that the designer of the tables in msdb (not just alerts) decided to store date and time information as ints. You will need to consider that when wanting to do date comparisons or conversions.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.