Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to Run DBCC CheckDB on Alternate SQL Server to Reduce Load on Primary Server


By:   |   Last Updated: 2019-01-21   |   Comments (4)   |   Related Tips: More > 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:

  1. Backup the database in question
  2. Restore the database to another instance of SQL Server.
  3. Run DBCC CheckDB on the copy of the database.
  4. 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


Last Updated: 2019-01-21


next webcast button


next tip button



About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 24, 2019 - 8:17:53 AM - Eric R Blinn Back To Top

 @Thomas - Your comment about licensing is a great one.  The corruption check operation is a production workload and would need to be licensed as such.  If creating a new box explicitly for this purpose consider using the server + cals licensing method as there would really only be one user on the machine.

Regarding the validity of the test I must disagree.  There is nothing about a backup and restore operation that would repair corruption of a database.  If there is corruption on the principal server it will come through on the secondary.

I'll refer to Paul Randal, link below, who writes, "...you either have to run the consistency checks on the production database, or on a restored backup of it, or you’re not testing the production database. Nothing else is good enough." 

https://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/


Thursday, January 24, 2019 - 2:39:49 AM - Thomas Franz Back To Top

Please remind, that "the other" SQL Server has to be licensed too (= 1000 - 7000 USD per core). You must NOT use a free developer edition (because you are working on productive data, even if is only a restore) nor the free standby license (because running CHECKDB is active usage and no "standby" action), that come with paying for Software Assurances (SA).

And of course there is no 100 % guarantee, that just because the restore is fine, the same is true for the original database (even if the chances are high).


Monday, January 21, 2019 - 12:59:53 PM - Eric Blinn Back To Top

@Doug - If you are taking an extra full backup to support this operation then using the COPY_ONLY option would be wise. Using that decoration will keep SQL Server from resetting the differential bitmap which, if reset unexpectedly, would mess up future differential backups.

However, one of the biggest advantages of this approach is that you can use the existing backups without adding any extra load to the principal server.  This also renders both the log sequence and differential bitmap problems moot.


Monday, January 21, 2019 - 10:51:48 AM - Doug Back To Top

I would STRONGLY suggest that you use WITH COPY_ONLY to keep from resetting any LSNs, which would affect future backups, especially if you are doing differential backups.


Learn more about SQL Server tools