By: Eric Blinn | Updated: 2019-01-21 | Comments (4) | Related: > Database Console Commands DBCCs
Problem
SQL Server DBCC CheckDB can sometimes run long for large databases and cause a performance impact in the production environment while it runs. In this tip we will look at a way to offload where DBCC CheckDB runs and help you build scripts to streamline the process.
Solution
Corruption checks are an incredibly important part of any SQL Server database maintenance routine. While the DBCC CheckDB command is not a blocking operation it is a very busy operation. This can cause problems when a database is required to be up 24x7 as it becomes difficult to find a time where the users will accept the performance degradation that can come from the operation. If this problem affects your systems, consider this approach where the corruption check workload can be offloaded to another instance.
The steps are relatively simple and will be covered in detail below. This post will give specific scripts that will work for an instance of SQL Server that uses native full database backups with or without differential backups that go to a single backup file. These same steps can be used for non-native backups or for striped backup files, but the scripts will have to be modified to account for those changes.
These are the steps:
- Backup the database in question
- Restore the database to another instance of SQL Server.
- Run DBCC CheckDB on the copy of the database.
- Analyze the results and take any necessary action.
Throughout this tip the AdventureWorks database will be used for the demo. This free sample database can be found at this link.
Step 1: Backup the SQL Server database to a file share
This step will be the simplest as the database in question should already be part of a backup plan. If not, consult this MSSQLTips article, Make a backup maintenance plan or refer to the Backup Tutorial and come back once complete.
If the files are sent to a local or non-shared volume the scripts can still work, but will need to be modified slightly to account for different file paths on the secondary versus on the primary.
To follow along this demo with a new database a backup can be taken manually. This gets run on the primary server.
SET NOCOUNT ON; DECLARE @DatabaseName SYSNAME = 'AdventureWorks'; BACKUP DATABASE @DatabaseName TO DISK = '\\FileServer\FileShare\RemoteCheckDBPractice.bak';
Step 2: Generate the database restore and CheckDB commands
Generate RESTORE DATABASE command
The system database MSDB keeps records of all database backups. This post and accompanying scripts will assume that any native backups taken are not renamed, moved, or deleted as MSDB cannot know these actions have taken place.
Below I will walk through the different sections of the code. You can download the complete code here.
This query will return the most recent full backup for a given database as well as the most recent differential, if one exists, that will attach to that same full backup. The script expects each backup to be created in its own backup file. It is this file or this file pair of files that will need to be restored to the secondary instance to be checked. Often this query is called from the secondary via a linked server back to the primary. For our example we'll run it manually from the primary server.
DECLARE @FullBackupName NVARCHAR(520);
DECLARE @DiffBackupName NVARCHAR(520);
DECLARE @backup_set_id INT;
SELECT TOP 1
@FullBackupName = fam.physical_device_name
, @DiffBackupName = famDIFF.physical_device_name
, @backup_set_id = bset.backup_set_id
FROM msdb.dbo.backupset bset
INNER JOIN
msdb.dbo.backupmediafamily fam ON bset.media_set_id = fam.media_set_id
LEFT OUTER JOIN
msdb.dbo.backupset bsetDIFF ON bset.backup_set_uuid = bsetDIFF.differential_base_guid AND bsetDiff.type = 'I'
LEFT OUTER JOIN
msdb.dbo.backupmediafamily famDIFF ON bsetDIFF.media_set_id = famDIFF.media_set_id
WHERE
bset.type = 'D'
AND
bset.server_name = @@SERVERNAME
AND
bset.database_name = @DatabaseName
AND
bset.is_copy_only = 0
ORDER BY
bset.backup_finish_date DESC
, bsetDIFF.backup_finish_date DESC;
Use the backup set ID from that query to determine the logical files that are part of that backup along with their size. Rather than call RESTORE FILELISTONLY, this data can be retrieved from the backupfile table in MSDB. This information can be used to verify that the required disk space is available before attempting the restore operation.
SELECT
logical_name
, file_type
, file_size/1024/1024 AS FileSizeMB
FROM
msdb.dbo.BackupFile
WHERE
backup_set_id = @backup_set_id
Combine this information about the backup files and database logical files to create a restore script for the database. This is best completed with dynamic SQL.
PRINT '--Run these commands on the secondary box';
DECLARE @SQL NVARCHAR(4000);DECLARE @TargetDataFilePath NVARCHAR(520);
DECLARE @TargetLogFilePath NVARCHAR(520);
SET @TargetDataFilePath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(520))
SET @TargetLogFilepath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(520))
SET @SQL = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' FROM DISK = ''' + @FullBackupName + ''' WITH ';
SELECT @SQL = @SQL + 'MOVE ''' + logical_name + CASE WHEN file_type = 'D' THEN ''' TO ''' + @TargetDataFilePath + logical_name + '.' + CASE WHEN file_number = 1 THEN 'm' ELSE 'n' END + 'df'','
ELSE ''' TO ''' + @TargetLogFilePath + logical_name + '.ldf'','
END
FROM
msdb.dbo.backupfile
WHERE
backup_set_id = @backup_set_id;
--We will not recover the database in case a differential needs to be applied
SET @SQL = @SQL + ' NORECOVERY;';
PRINT @SQL;
--EXEC sp_executeSQL @SQL;
--Restore DIFF, if necessary
IF @DiffBackupName IS NOT NULL
BEGIN
SET @SQL = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' FROM DISK = ''' + @DiffBackupName + ' WITH ';
SELECT @SQL = @SQL + 'MOVE ''' + logical_name + CASE WHEN file_type = 'D' THEN ''' TO ''' + @TargetDataFilePath + logical_name + '.' + CASE WHEN file_number = 1 THEN 'm' ELSE 'n' END + 'df'','
ELSE ''' TO ''' + @TargetLogFilePath + logical_name + '.ldf'','
END
FROM
msdb.dbo.backupfile
WHERE
backup_set_id = @backup_set_id;
--We still don't recover the DB so that we can be certain of its condition later on
SET @SQL = @SQL + ' NORECOVERY, REPLACE;';
PRINT @SQL;
--EXEC sp_executeSQL @SQL;
END;
--RECOVER the database since we are certain it has not been recovered
PRINT 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + ' WITH RECOVERY;';
Generate Update Usage command
One final command may be necessary after the database was restored. If restoring to a newer version of SQL Server then DBCC UPDATEUSAGE must be called. Without this operation CheckDB often returns false negatives.
PRINT 'DBCC UPDATEUSAGE(' + @DatabaseName + ');';
Generate DBCC CheckDB command
This DBCC CheckDB call doesn't have any special requirements. The newly restored DB is not any different than any other database. The only option that would be wise to include would be WITH TABLOCK. Calling DBCC CheckDB WITH TABLOCK tells SQL Server to NOT make a snapshot before starting the operation. That reduces the workload slightly at a cost of making the command a blocking operation. As the CheckDB command is the only user that should be in this database, including the TABLOCK decoration is likely safe to use.
PRINT 'DBCC CHECKDB (' + @DatabaseName + ') WITH TABLOCK;';
Step 3: Run the generated commands on the secondary server
The above code sections when run together will generate the necessary restore command and other commands that would be run on the secondary server. This will be on the Message tab in SSMS and you can copy and paste the code and run on the secondary server to restore the database, update usage and run the CheckDB.
Step 4: Analyze the DBCC CheckDB results and take necessary action
If the DBCC CheckDB command succeeds then that means the original database, its backup file(s), and the restored copy are all without corruption. At that point simply delete the copy of the database and move on.
If the DBCC CheckDB command fails that doesn't necessarily mean the database is corrupt. It could indicate a failure at any step of the way. In the event a failure occurs on the secondary, the next step should be to run a DBCC CheckDB on the principle server to see if the issue persists. The expectation is that this won't happen often and the number of times a DBCC command must be run against the primary server will be less than 1% of the time.
Final thoughts
This method includes a lot of extra steps, but the workload savings on a large SQL Server can be astronomical and worth the investment of time in creating this routine. The backup operation on the principle is an operation that should already be happening so there is no extra load there. The queries against MSDB are tiny and should not have any effect on any reasonable SQL Server. All of the remaining effort falls on the disks supporting the backup files, which should be separate from the disks servicing the principle SQL Server workload, and the secondary SQL Server designated to handle this workload.
As an added bonus, the backups are going to be tested on a daily or weekly basis as the CheckDB operation runs. Backup verification tests are something that a good DBA should be doing from time to time anyway, but are often an overlooked part of proper database maintenance. Getting this test as a freebie is a pretty good deal.
Next Steps
- You can download the complete code here.
- Take a look at these related items:
About the author
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-01-21
