Introduction
In this article we will set up an email notification at the SQL Server level whenever SQL Server and the SQL Server Agent Restarts. The article will useful for DBA’s who support a large number of database servers which they cannot manually connect to daily to check the SQL Server status, SQL job status or SQL Server Agent status.
Please note that there are some OS level notifications and/or third party tools available that will notify you as soon as SQL Server or SQL Server Agent goes down but here we will discuss how to get notified at SQL Server level.
Brief on SQL Server Agent Restart
Many times we encounter a scenario where some SQL Server jobs didn’t run because the SQL Server Agent was down and most of the times, the application user feels the actual impact of those jobs after a few weeks or months. In such circumstances, if application partners come to DBA’s to check what was the status of a particular job on a particular day (says 1 month back), usually a DBA will not be able to find the status because normally, job histories are kept only for a week or two.To overcome this scenario we can setup an email notification which will notify the DBA’s whenever the SQL Server Agent was restarted, so that the DBA can immediately connect to the server) and check the reason behind the SQL Server Agent restart as well as the job status in case there were some scheduled jobs that didn’t run because the SQL Server Agent was down during that timeframe.
Brief on the SQL Server Restart
It is often the case that SQL Server is restarted because the infrastructure team implemented an OS change and rebooted the system without the knowledge of the DBA. It is possible after this restart that some databases were not able to come up online because recovery failed or some data-files may have been corrupted. Since all this happens behind the scenes, there is no way a DBA will know about a possible issue unless he has some automated systems set in place beforehand. One possible way to accomplish this is to set up an email notification when the SQL Server is restarted.
The best way to get notified when SQL Server Restarts is to create a stored procedure in the Master database (the reason being, it is only the Master database that has the facility to set up execution time of the stored procedure at SQL Server startups). Then schedule this stored procedure to run at every SQL Server startup (you will find the option when you go to the properties of stored procedure in Enterprise Manager or SSMS).
This stored procedure will send the notification email when the SQL Server restarts and will trigger a job, which will check the status of all databases and will send the report by email.
Scripts and Short Description
Here I will briefly explain each script that is being used.
1) SQL Sever Restart (download)
As the name suggests this script will send a notification when SQL Server restarts. In this script, we are primarily creating a Stored Procedure ‘sp_SqlServerRestartNotificationMail’ on the Master database. This stored procedure will be scheduled to execute when the SQL Server restarts. Below is the command from the script, which is used to schedule the stored procedure at SQL Server Startup.
Exec sp_procoption N’sp_SqlServerRestartNotificationMail’, N’startup’, N’true’
The body of this SP has two steps. The first is an Sql Mail script using which we will send email notifications and second is to trigger a job “DB Status after SQL Restart” (This job will be created as step 3.b of implementation instructions.)
2) DB Status after SQL Restart (download)
This script creates a Job that will collect the details of all databases residing on the server (by querying the sysdatabases table in the Master database) and collect the status of each database. It checks if the database is online, whether it is in read write or read only mode, and if it is in a single user mode or multi-user mode. All this information is collected and sent through email to the recipients. Also, in case of a clustered environment it will update you with the current active node.
3) SQL Server Agent Restart (download)
This script creates a job by name ‘SQL Server Agent Restart’, which is scheduled to execute whenever the SQL Server Agent is started.
The content of this job is a script that will send email, notifying about SQL Server Agent restart along with the database server details.
Another place where you can make use of this script (just by modifying the notification message) is to get notified whenever a failover happens in a clustered environment. Since, whenever a failover happens the SQL Server Agent restarts, this script will execute a job and will notify you about the failover.