若 SQL Server 意外停止後,重新叫起並發出通知

可以讓 Agent Services 自動重新叫起 SQL Server Services

image

或直接透過 Windows 的 Services 管理介面自動重起,第三次重起可以藉由批次命令,或 Script 叫起 Services 並發出通知

image

在此簡單地以 vbscript 撰寫讓 Service 重起後,可以寄發通知的腳本

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@tfs2010"
objMessage.To = "test@tfs2010"
objMessage.TextBody = "This is some sample message text."
objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2  ‘1:local pickup 2: remote
objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.0.1.3"
objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send

不管 SQL Server 是透過 Windows 的 Services 管理或 Agent Services 叫起,SQL Server 啟動後,可以直接執行預存程序,透過 DB Mail 寄發通知

exec sp_configure ‘show advanced options’,1
reconfigure
exec sp_configure ‘scan for startup procs’,1
reconfigure
–重新啟動 SQL Server
go
CREATE PROC spNotifyStart
AS
DECLARE @bodyString NVARCHAR(200)=’SQL Server started at ‘ + CONVERT(NVARCHAR(30),GETDATE())
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘myProfile’,  — 要自己先定義一個 DB Mail Profile
    @recipients = ‘DBA@tfs2010.com’,
    @body = @bodyString,
    @subject = ‘SQL Server Start’ ;
GO
exec sp_procoption ‘spNotifyStart’,’startup’,’true’

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: