Introduction

If you are already using Ola’s maintenance script (you should if you don’t) to facilitate SQL Server routine maintenance on your private or public cloud (Azure VM/AWS EC2/GCP CE), you are familiar with Ola’s Maintenance. In a nutshell, Ola’s Maintenance Solution allows for a few easy steps to complement SQL Server installation with routine basic maintenance tasks – backups, corruption checks, and index and statistics.

While SQL Server does a great job of being crash consistent, sometimes you want to make sure that your data didn’t get corrupted after an unexpected reboot/restart.

The following blog assumes that you are using Ola Hallengren maintenance scripts and is going to offer a solution to use Ola’s database corruption procedure when a crash is detected.

Problem

A common reaction to an unexpected reboot/restart is to run DBCC CHECKDB to be extra sure that you don’t have any corruption or at least discover it ASAP and fix it. What happens if you want to do it unattended, i.e., automate it? Can you have a process that discovers a server/service reboot and runs DBCC CHECKDB so you, as a DBA, don’t have to do it?

Solution

Introducing sys.dm_server_services DMV, and of course, the unmatched easy-to-use Ola’s maintenance scripts.

We will be querying sys.dm_server_services to see if there was a recent (@Look_back_in_minutes controls how much recent) SQL Server service restart. We will be using DatabaseIntegrityCheck to make calling DBCC CHECKDB as easy as possible.

The following solution on GitHub will create a job step scheduled to run every minute to discover SQL Server service restarts, which will trigger a faster CHECKDB data corruption check.

Job creation
https://github.com/steverezhener/DBA/blob/master/Jobs/_Maintenance_08_Post_Crash_CheckAlldbs_01.sql

Procedure to run CHECDB DBCC PHYSICAL_ONLY
https://github.com/steverezhener/DBA/blob/master/Procedures/maintenance.dbcc_physical_only.sql