As a Production DBA, it is crucial to keep a close eye on the activities happening on your database servers. This includes monitoring jobs, backups, disk space, and database changes. In this article, we will discuss how to automate these monitoring activities and receive the information in a well-formatted email.

Server Info

The first section of the email provides generic information about the server, such as the server name, SQL Server version, service pack, and licensing information. This information is fetched from various system objects and displayed in a table format.

SET @TableHTML =   
     '<font face="Verdana" size="4">Server Info</font>  
     <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">  
     <tr>  
     <td width="27%" height="22" bgcolor="#000080"><b> <font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td> <td width="39%" height="22" bgcolor="#000080"><b>  
     <font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>  
     <td width="90%" height="22" bgcolor="#000080"><b>  
     <font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td> 
     </tr>  
     <tr> <td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td> <td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>  
     <td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>  
     </tr>  
     </table>

This section provides essential information about the server, making it easier for the DBA to identify the server and project/client associated with it.

Job Status

The next section focuses on job status, including whether the jobs have executed successfully or failed, and the duration of the execution. Failed jobs are highlighted in red with links that open the email editor, allowing the DBA to send failure information to the concerned person.

The job information is fetched using the system procedure sp_help_job in the msdb database. The output is manipulated and displayed in a table format.

Databases

This section provides a list of databases on the server, along with attributes such as creation date, size, state, and recovery model. It helps the DBA identify any new databases or drastic increases in database size.

The information is fetched from catalog views sys.databases and sys.sysaltfiles and displayed in a table format.

Disk Stats

In this section, the DBA can see how much free space is left on individual disks. This information is fetched using the undocumented extended stored procedure xp_fixeddrives and displayed in a table format.

SQL Server Database Backup Stats

This section provides information about database backups, including the databases that were backed up, backup filenames, backup types, start time, end time, and backup size. The information is fetched from the backupsets catalog view and displayed in a table format.

Physical Backup Files

This section displays a list of physical backup files residing on the server. It helps the DBA ensure that old backup files are deleted according to the retention policy and identifies any unexpected files. The information is fetched from the backupmediafamily catalog view and displayed in a table format.

By automating these monitoring activities and receiving the information in a well-formatted email, the DBA can save time and easily keep track of the server’s health. The email can be scheduled to run daily or as per the DBA’s requirement.

To set up this automated health check email, follow these steps:

1. Run the attached script (EmailSQLServerHealth.sql) inside the msdb database. This will create a stored procedure (uspEmailSQLServerHealth) that accepts input parameters.
2. Schedule a job with a call to the stored procedure, providing the necessary input parameters.

By implementing this solution, the DBA can efficiently monitor multiple database servers and receive daily health check reports without manual intervention.

About the Author:

The author is a Microsoft Certified DBA with over 5 years of experience. They hold certifications in MCTS and MCITP and have extensive experience in managing production database servers.