Implementation
Before you implement these scripts on your servers, please follow the Pre-Implementing checklist below:-
1) Send email part in all scripts is to be tested on respective servers and modified accordingly.
—————————————————-
EXEC master.dbo.xp_smtp_sendmail @FROM = ‘test@test.com‘, — Sender Email Id
— @FROM_NAME = @From_Name,
@TO = ‘test1@test.com‘, — Recipient Email Id
— @replyto = @Reply_Address,
— @CC = @Copy_Address,
— @priority = @Priority,
@subject = @msgg,
@type = ‘text/plain’,
@message = @msg,
— @attachments = ‘T:job_output.txt’,
@SERVER = N’111.111.111.111′ — IP where Mail Server is installed
—————————————————-
2) Script ‘DB Status after SQL Restart’ has an output file path for step2 as ‘T:job_output.txt’. We have to change it to the actual path according to the respective server.
Furthermore, ‘T:job_output.txt’ in step3 is to be replaced by the actual path of output file in Step2 of this job.
3) No need to schedule ‘DB Status after SQL Restart’ job. This job will be triggered automatically by SP sp_SqlServerRestartNotificationMail.
Now, to implement these scripts you can follow the below instructions:-
1) Connect to the respective server.
2) Complete ‘Pre-Implementing checklist’.
3) Execute the script below scripts in master db:-
a. SQL Sever Restart
b. DB Status after SQL Restart
c. SQL Server Agent Restart
4) You are all set.
Incase something goes wrong or you just want to fallback, please use the below fallback instructions:-
1) Connect to the respective server.
2) Drop the SP sp_SqlServerRestartNotificationMail in master DB.
3) Drop the jobs ‘SQL Server Agent Restart’ and ‘DB Status after SQL Restart’.
4) Something went wrong during implementation.
Conclusion
I usually get an email from our application partners saying some of their jobs have not performed the task that should have done only to find that when we connect to the server that SQL Server Agent was down during that time. After implementing these scripts, we receive email notifications when SQL Server Agent restarts helping us to find the jobs that didn’t run because of this issue and act accordingly.
]]>