As a database administrator (DBA), your daily routine often involves performing various health checks on your SQL Server instances. These checks ensure that your databases are running smoothly and any potential issues are identified and addressed proactively. However, manually performing these checks can be time-consuming and repetitive. In this article, we will explore the concept of automating database health checks to save time and improve efficiency.

System Architecture

To automate database health checks, you can create a custom monitoring solution using scheduled processes or jobs. These processes will run SQL scripts that query the system databases and catalog views of your SQL Server instances. It is recommended to create a central “monitoring database” where you store the necessary stored procedures and functions for the health checks.

There are two approaches to accessing the servers for monitoring. The first approach is to keep a central copy of the monitoring database and execute the procedures against the remote servers. The second approach is to have a copy of the monitoring database in each monitored instance. Both approaches have their own requirements and considerations.

The monitoring instance, where the monitoring database is hosted, does not need to be a high-performance machine. It can be a virtual machine, an old development box, or even the DBA’s workstation. The version of SQL Server hosting the monitoring database will depend on the current environment.

To send out periodic reports about the health status of the servers and databases, you will need to configure the monitoring instance for sending emails using Database Mail or SQL Mail, depending on the SQL Server version. Additionally, you will need to create linked servers in the monitoring instance pointing to each SQL Server in the network, and a dedicated linked server in each monitored instance pointing back to the central server.

What to Monitor, How to Monitor, When to Monitor

Once you have set up the monitoring server and the monitoring database, you can start developing the health checks. Here are some ideas:

Heartbeat

Implement a “heartbeat” mechanism where each SQL Server instance sends a small token message to the monitoring server to indicate that it is alive. The monitoring server periodically checks this information and sends a warning message if a heartbeat has not been received within a predefined interval. This helps you quickly identify if a SQL Server instance is down.

Database Status

Check the status of your databases across all monitored servers using the DATABASEPROPERTY function. This allows you to identify if any databases are in suspect, read-only, single-user, offline, or shut down mode. You can schedule this check to run once every day, particularly just before the normal working hours start.

Job Monitor

Monitor the status of jobs running on your SQL Server instances. Instead of configuring notifications for each individual job, you can query the sysjobs and sysjobhistory tables in the msdb database to identify jobs that have failed within a specified time window. You can schedule this check to run once every morning or once every hour, depending on the criticality of the jobs.

Backup Status

Ensure that your database backups are running successfully. Query the backupset and backupfile tables in the msdb database to find out the last time each database was fully backed up. This helps you identify if any databases have been left out of the backup plan. Schedule this check to run once every day after the scheduled full backup window.

File and Disk Space Monitor

Regularly check the server disk space to ensure databases have enough space to grow. Use the xp_fixeddrives extended stored procedure to find the free space available in a server’s local drives. Additionally, query the sp_helpfile system stored procedure against each database to identify data files approaching their maximum size. Schedule the file space monitor to run once every week and the disk space monitor at least once or twice a day.

Conclusion

Automating SQL Server database health checks can significantly improve the efficiency of a DBA’s daily tasks. By implementing a custom monitoring solution, you can proactively identify and address potential issues, saving time and effort. The health checks discussed in this article are just the minimum requirements, and you can customize them based on your specific needs. With automated monitoring in place, you can ensure the smooth operation of your SQL Server environment.