Simple Corruption Check for all SQL Server databases

Problem

As DBAs, we all know how important it is to check all of the SQL Server databases under our care for potential corruption issues. However, to accomplish that, we might not have the time to do any of the following:

• Setup a maintenance plan across all X SQL Server instances under our support.
• Setup a custom job to run a consistency check across all databases in all X SQL Server instances (which this tip particularly aims to address).
• Build a PowerShell solution (if you want to get a bit fancy here).

Regardless of the way you choose to go, you have to consider other important factors as well:

• Environment type (production / non-production)
• When is the maintenance window?
• How much time do you have during the maintenance window?

Solution

I have come up with a custom *simple solution that lets you check the consistency of all the databases under your care, save only the end result (without that much overhead) with a few additional important values, while having to modify only a couple of things that are very specific to your use case.

*By simple I’m not implying that the amount of code used is small, but instead simple in terms of what it accomplishes in the end.

SQL Server DBCC CHECKDB Script as a whole

The entire script consists of 3 sections:

• Creation of the table to store the final results.
• Creation of the custom stored procedure.
• Creation of the job to automate it.

You can find complete “plug-and-play” script, in case you want to test it right away, attached within this tip.

I will be detailing each section in the next sub-sections.

Creation of the SQL Server DBCC CheckDB table

This will be used to store the result of the execution of the DBCC CHECKDB statement against each database.

• You can use a different database, I used the master just as an example.
• Here’s the description for the fields that might not be so obvious at a first glance:
  • [checkdb_type] -> ‘FULL’ or ‘PHYSICAL ONLY’ (will explain later the reason for these 2 values in the Stored Procedure).
  • [completion_time] in seconds because it is very useful to know how long a CHECKDB against each database took. You can even use this information to plan your checks in a more efficient way because you can get an average of the time it takes to complete for each database.
  • [last_good_dbcc] tells you when was the last time a successful CHECKDB took place, something very important to know so that later on you can prioritize those that haven’t had a check in a while (for whatever reason).

USE [master]
GO
CREATE TABLE [dbo].[CheckDB](
   [instance] [varchar](255) NOT NULL,
   [database] [varchar](255) NOT NULL,
   [size] [int] NOT NULL,
   [result] [varchar](max) NULL,
   [checkdb_type] [varchar](255) NULL,
   [data_collection_timestamp] [smalldatetime] NULL,
   [completion_time] [int] NULL,
   [last_good_dbcc] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Creation of the SQL Server DBCC CheckDB Stored Procedure

At a first glance the stored procedure might seem quite big, but it is only because it contains some validations and considerations that you probably won’t find in similar versions.

Here are the most important points to note:

• From the very start, you can see that the stored procedure can accept 2 parameters:
  • @dbSizeThreshold: by default, it is set at 512,000 MB (500GB) and it is used to determine if the CHECKDB will use the “PHYSICAL_ONLY” option for databases larger than 500GB (you can set it to whatever you think fits your case). Let’s keep in mind that your maintenance window might not be enough to accommodate a full CHECKDB for a big database (or databases), and a run with “PHYSICAL_ONLY” option might represent at least a small victory for you.
  • @force: this parameter is designed to override the @dbSizeThreshold behavior. For instance, you might have an instance with only 1 big database (bigger than the threshold value) but you feel confident enough that a full CHECKDB can be successful within your maintenance window, so you go for it and set it to 1 to proceed with a full run of the CHECKDB.

• This stored procedure takes into account the SQL Server version where it’s being executed, so you don’t have to worry to deploy a different version depending on how old yours is.
  • Since we are executing the CHECKDB command WITH TABLERESULTS, there is a slight variation in the output obtained starting from SQL Server 2012 (since it adds more fields to the mix), but the stored procedure creates the right table for you.

• You might notice that I use a cursor to traverse the database list and run the CHECKDB against each one of them.
  • I know that cursors are somewhat of a “taboo” thing within our profession, for their low performance, but its purpose is purely for control.
  • First I populate the table “master.dbo.CheckDB” with the databases to be checked by the stored procedure. As each one is checked successfully, then the respective record is updated in the table.
  • By doing this, you know if a particular database was not a part of the process.
    • Server might have been patched and rebooted while the execution was taking place.
    • Server was unexpectedly shutdown while the execution was taking place.
    • The SQL Server instance was restarted while the execution was taking place.
  • When CHECKDB is executed against the master database, the output for the ResourceDB is also obtained, so I also make sure to capture that one as well.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Simple_CHECKDB]    Script Date: 8/30/2018 3:45:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Alejandro Cobar
-- Create date: 8/22/2018
-- Description:Runs DBCC CHECKDB ON each database and stores the output message 
-- =============================================
CREATE PROCEDURE [dbo].[Simple_CHECKDB]
@dbSizeThreshold INT = 512000,
@force INT = 0
AS
BEGIN
SET NOCOUNT ON;
-- Temporal table to obtain the "Last Known Good DBCC CHECKDB" execution for each database
IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL  
DROP TABLE #DBInfo; 
CREATE TABLE #DBInfo
([ParentObject] VARCHAR(255)
,[Object] VARCHAR(255)
,[Field] VARCHAR(255)
,[Value] VARCHAR(255)
)
-- Depending on the SQL Server version, the respective temporal table will be created to store the CHECKDB results
DECLARE @version INT;
SELECT @version = RIGHT(LEFT(@@VERSION,25),4);
--Starting from SQL Server 2012, new fields were introduced to the output of DBCC CHECKDB WITH TABLERESULTS
IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL  
DROP TABLE #CheckDB_old; 
IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL  
DROP TABLE #CheckDB_new;
IF @version >= 2012
CREATE TABLE #CheckDB_new 
([Error] INT 
,[Level] INT 
,[State] INT 
,[MessageText] VARCHAR(MAX) 
,[RepairLevel] INT 
,[Status] INT 
,[DbId] INT 
,[DbFragId] INT 
,[ObjectID] INT 
,[IndexId] INT 
,[PartitionId] INT 
,[AllocUnitId] INT 
,[RidDbId] INT 
,[RidPruId] INT 
,[File] INT 
,[Page] INT 
,[Slot] INT 
,[RefDbID] INT 
,[RefPruId] INT 
,[RefFile] INT 
,[RefPage] INT 
,[RefSlot] INT 
,[Allocation] INT);
ELSE
CREATE TABLE #CheckDB_old 
([Error] INT 
,[Level] INT 
,[State] INT 
,[MessageText] VARCHAR(MAX) 
,[RepairLevel] INT 
,[Status] INT 
,[DbId] INT 
,[ObjectID] INT 
,[IndexId] INT 
,[PartitionId] INT 
,[AllocUnitId] INT 
,[File] INT 
,[Page] INT 
,[Slot] INT 
,[RefFile] INT 
,[RefPage] INT 
,[RefSlot] INT 
,[Allocation] INT);
-- We don't want to keep all the CHECKDB results here for a very long time...
TRUNCATE TABLE master.dbo.CheckDB;
-- Insert all the databases that will be checked
-- Only consider those in ONLINE state and exclude the SNAPSHOTS
INSERT INTO master.dbo.CheckDB
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(CAST(mf.size AS BIGINT)*8)/1024, NULL, NULL, NULL, NULL,NULL
FROM sys.master_files mf
JOIN sys.databases db ON mf.database_id = db.database_id
WHERE db.state = 0 AND db.source_database_id IS NULL
GROUP BY mf.database_id;
-- Prepare a cursor to have a better control of which databases where checked and which weren't
-- A sudden server or instance reboot might affect this whole process...
DECLARE @db VARCHAR(255);
DECLARE checkdb_cursor CURSOR FOR 
SELECT [database] 
FROM master.dbo.CheckDB
WHERE result IS NULL;
OPEN checkdb_cursor  
FETCH NEXT FROM checkdb_cursor INTO @db
WHILE @@FETCH_STATUS = 0  
BEGIN  
DECLARE @startTime DATETIME;
DECLARE @endTime DATETIME;
      DECLARE @databaseSize BIGINT;
SELECT @databaseSize = size FROM master.dbo.CheckDB WHERE [database] = @db;
SET @startTime = GETDATE();
IF @databaseSize <= @dbSizeThreshold OR @force = 1
BEGIN
IF @version >= 2012
BEGIN
INSERT INTO #CheckDB_new 
([Error], [Level], [State], [MessageText], [RepairLevel], 
[Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId], 
[AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID], 
[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation]) 
EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS');
SET @endTime = GETDATE();
UPDATE master.dbo.CheckDB
SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
FROM #CheckDB_new
WHERE [Error] = 8989 AND [database] = @db;
IF @db = 'master'
INSERT INTO master.dbo.CheckDB 
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
FROM #CheckDB_new
WHERE [Error] = 8989 AND DbId = 32767;
TRUNCATE TABLE #CheckDB_new;
END;
ELSE
BEGIN
INSERT INTO #CheckDB_old
([Error], [Level], [State], [MessageText], [RepairLevel], 
[Status], [DbId], [ObjectID], [IndexId], [PartitionId], 
[AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation]) 
EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS');
SET @endTime = GETDATE();
UPDATE master.dbo.CheckDB
SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
FROM #CheckDB_old
WHERE [Error] = 8989 AND [database] = @db;
IF @db = 'master'
INSERT INTO master.dbo.CheckDB 
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
FROM #CheckDB_old
WHERE [Error] = 8989 AND DbId = 32767;
TRUNCATE TABLE #CheckDB_old;
END;
END;
ELSE
BEGIN
IF @version >= 2012
BEGIN
INSERT INTO #CheckDB_new 
([Error], [Level], [State], [MessageText], [RepairLevel], 
[Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId], 
[AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID], 
[RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation]) 
EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS, PHYSICAL_ONLY');
SET @endTime = GETDATE();
UPDATE master.dbo.CheckDB
SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
FROM #CheckDB_new
WHERE [Error] = 8989 AND [database] = @db;
IF @db = 'master'
INSERT INTO master.dbo.CheckDB 
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
FROM #CheckDB_new
WHERE [Error] = 8989 AND DbId = 32767;
TRUNCATE TABLE #CheckDB_new;
END;
ELSE
BEGIN
INSERT INTO #CheckDB_old
([Error], [Level], [State], [MessageText], [RepairLevel], 
[Status], [DbId], [ObjectID], [IndexId], [PartitionId], 
[AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation]) 
EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS, PHYSICAL_ONLY');
SET @endTime = GETDATE();
UPDATE master.dbo.CheckDB
SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)
FROM #CheckDB_old
WHERE [Error] = 8989 AND [database] = @db;
IF @db = 'master'
INSERT INTO master.dbo.CheckDB 
SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL
FROM #CheckDB_old
WHERE [Error] = 8989 AND DbId = 32767;
TRUNCATE TABLE #CheckDB_old;
END;
END;
-- Get the information for the "Last Known Good DBCC CHECKDB" execution 
INSERT INTO #DBInfo ([ParentObject], [Object], [Field], [Value]) 
    EXEC ('DBCC DBINFO(['+@db+']) WITH TABLERESULTS');
UPDATE master.dbo.CheckDB
SET last_good_dbcc = [Value]
FROM #DBInfo
WHERE [Field] = 'dbi_dbccLastKnownGood' AND [database] = @db;
IF @db = 'master'
UPDATE master.dbo.CheckDB
SET last_good_dbcc = (SELECT last_good_dbcc FROM master.dbo.CHECKDB WHERE [database] = 'master')
WHERE [database] = 'mssqlsystemresource';
TRUNCATE TABLE #DBInfo; 
FETCH NEXT FROM checkdb_cursor INTO @db 
END
CLOSE checkdb_cursor  
DEALLOCATE checkdb_cursor  
-- Drop whichever temporal table was created
IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL  
DROP TABLE #CheckDB_old; 
IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL
DROP TABLE #CheckDB_new; 
IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL  
DROP TABLE #DBInfo; 
END
GO

Creation of the DBCC CheckDB SQL Server Agent Job

Depending on your particular case, you might want to accommodate the schedule of the job to fit your maintenance window.

• You might create a version for your production environment and another for your non-prod environment and start the deployment from there.
• The modifications you probably would want to make are the following:
  • This example uses the ‘sa’ account, but you can use a different one.
  • You can change the name of the job.
  • You can change the schedule of the job.

USE [msdb]
GO
/****** Object:  Job [Database Consistency Check]    Script Date: 8/28/2018 5:53:06 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 8/28/2018 5:53:06 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Database Consistency Check', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'Runs a DBCC CHECKDB operation against all the databases in the SQL Server instance', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CheckDB]    Script Date: 8/28/2018 5:53:07 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckDB', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'EXEC Simple_CHECKDB', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CHECKDB Execution', 
@enabled=1, 
@freq_type=8, 
@freq_interval=64, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=1, 
@active_start_date=20180822, 
@active_end_date=99991231, 
@active_start_time=200000, 
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

SQL Server DBCC CHECKDB Demo

Let me present you a few images of the output stored in the CheckDB table, for possible execution cases.

The execution times for the StackOverflow database are a bit high because I have those test databases in an external mechanical hard drive (5400 RMP), so that kind of slowness is expected.

Normal Execution:

EXEC Simple_CHECKDB

Modifying the @databaseSizeThreshold parameter:

EXEC Simple_CHECKDB @dbSizeThreshold = 100000

Notice the “PHYSICAL ONLY” result and it took 1,356 sec. less to complete.

Modifying the @force parameter:

EXEC Simple_CHECKDB @force = 1, @dbSizeThreshold = 5

Even if the database size parameter, to trigger a “PHYSICAL_ONLY” run, matches with the actual sizes of the databases to check, the @force parameter will “force” the CHECKDB to be a full one.

Bonus Content – PowerShell Version to Collect SQL Server DBCC CHECKDB Results

Attached within this tip you can also find a custom PowerShell that allows you to grab all the CheckDB results from a set of SQL Server instances and store them in a central database that you might have.

• You will have to modify some parameters, but I’ve put XXX where your values should go to make it work.

$labServer = "XXX"
$inventoryDB = "XXX"

#Fetch all the instances to connect to
$instanceLookupQuery = "SELECT instance from XXX"
$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery
if($args[0].Length -gt 0 -and $instances -eq $null){
Write-Host "Instance doesn't exist"
break
}
#For each instance, grab the CheckDB results from master.dbo.CheckDB
foreach ($instance in $instances){
$CheckDBQuery = "SELECT * FROM master.dbo.CheckDB"
Write-Host "Fetching CheckDB results for instance" $instance.instance
try{$results = Invoke-Sqlcmd -Query $CheckDBQuery -ServerInstance $instance.instance -ErrorAction Stop -querytimeout 30}
         catch{Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query "INSERT INTO XXX VALUES('CheckDB','$($instance.instance)','$($_.Exception.Message)',GETDATE())"}
#Perform the INSERT in the XXX table only if it returns information
   if($results.Length -ne 0){
    
#Build the insert statement
$insert = "INSERT INTO XXX VALUES"
foreach($result in $results){        
            $result_text = $result['result'] -replace "'","''"
            
$insert += "
(
'"+$result['instance']+"',
'"+$result['database']+"',
"+$result['size']+",
'"+$result_text+"',
'"+$result['checkdb_type']+"',
            '"+$result['data_collection_timestamp']+"',
            "+$result['completion_time']+",
            '"+$result['last_good_dbcc']+"'
            ),
"
     }
#Store the results in the local XXX table in our Lab Server instance
Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
}
}
Write-Host "Done!"

Next Steps

• This code has been tested in the following SQL Server versions: 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017.
• I’m all ears for any suggestions, improvements, recommendations regarding my code; which, in fact, I’m planning to keep updating to make it even better.
• I have seen a lot of debate on whether or not you should run a CHECKDB against the tempdb. You should check out this post by Kendra Little.
• Check out all of the SQL Server DBCC CHECKDB tips.

Alejandro Cobar

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

• MSSQLTips Awards: Author of the Year – 2020 | Rising Star (50+ tips) – 2021 | Author Contender – 2019, 2021