下表列出和描述 SQL Server Database Engine 所產生之錯誤的嚴重性層級。
以下的實作練習,將建立「警示」,來回應Database Engine 錯誤嚴重性(Error Severities)
回應的嚴重性層級,是在層級 17 以上,而且是已經寫入到 Windows 應用程式記錄中。
任務1:建立「警示」來回應嚴重性層級在17以上的錯誤事件
請執行以下的範例程式碼:
PRINT N'準備安裝「警示」...' GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 17 錯誤') OR ((severity = 17) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 17 錯誤', @message_id = 0, @severity = 17, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 18 錯誤') OR ((severity = 18) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 18 錯誤', @message_id = 0, @severity = 18, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 19 錯誤') OR ((severity = 19) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 19 錯誤', @message_id = 0, @severity = 19, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 20 錯誤') OR ((severity = 20) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 20 錯誤', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 21 錯誤') OR ((severity = 21) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 21 錯誤', @message_id = 0, @severity = 21, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 22 錯誤') OR ((severity = 22) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 22 錯誤', @message_id = 0, @severity = 22, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 23 錯誤' OR ((severity = 23) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 23 錯誤', @message_id = 0, @severity = 23, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 24 錯誤') OR ((severity = 24) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 24 錯誤', @message_id = 0, @severity = 24, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE (name = N'發生:嚴重層級. 25 錯誤') OR ((severity = 25) AND (message_id = 0) AND (database_name IS NULL) AND (event_description_keyword IS NULL) AND (performance_condition IS NULL)))) EXECUTE msdb.dbo.sp_add_alert @name = N'發生:嚴重層級. 25 錯誤', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 10, @database_name = NULL, @notification_message = NULL, @job_name = NULL, @event_description_keyword = NULL, @include_event_description_in = 5; -- Email and NetSend GO PRINT '' PRINT N'已經完成安裝.' GO -- 查詢已經安裝的「警示」 EXECUTE msdb.dbo.sp_help_alert; GO
請參考下圖所示:
由上,已經完成建立「警示」的作業。
請再整合到「操作員(Operator)」上,設定發生這些「警示」時,要使用「操作員」進行通知作業。
若是要刪除先前所建立的「警示」,可以執行以下的範例程式碼:
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 17 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 17 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 18 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 18 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 19 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 19 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 20 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 20 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 21 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 21 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 22 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 22 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 23 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 23 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 24 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 24 錯誤' GO IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'發生:嚴重層級. 25 錯誤') EXEC msdb.dbo.sp_delete_alert @name=N'發生:嚴重層級. 25 錯誤' GO
參考資料:
監視和回應事件
http://msdn.microsoft.com/zh-tw/library/ms191508.aspx
Database Engine 錯誤嚴重性
http://msdn.microsoft.com/zh-tw/library/ms164086%28SQL.90%29.aspx
讀我檔案_警示範例
http://technet.microsoft.com/zh-tw/library/ms161554.aspx
Microsoft SQL Server Community Projects & Samples
http://sqlserversamples.codeplex.com/Wikipage
Microsoft SQL Server Product Samples: Scripts
http://msftscrptprodsamples.codeplex.com/Wikipage
沒有留言:
張貼留言