Notifications for SQL Server Agent Restarts

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.

Pages: 1 2




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |