How to check the uncheckable: DBCC on VLDB


How to check the uncheckable: DBCC on VLDB


One of our customers had problems maintaining some monster databases (VLDB). These have about 2 to 4 TB of data and more than 90,000 tables. Building a good maintenance solution for such databases isn’t easy. For most of my clients I use the Ola Hallengren solution, which has proved to be excellent before but it just wasn’t suitable here. The first thing on my list was getting the databases checked. But the normal DBCC CHECKDB command went on for days until its snapshot clogged up my server. The WITH PHYSICAL_ONLY option didn’t help either. And checking all the tables separately would have taken way too much time. So I decided to do some reading online and found an answer in Paul Randall’s blog.

The basic concept of Paul’s blog is to chop up your CHECKDB into CHECKTABLE, CHECKALLOC and CHECKCATALOG. He suggests doing this on a weekly basis. So seven days of CHECKTABLE and two days of CHECKALLOC and CHECKCATALOG. Since I was going to have to do this for a lot of databases, I decided to turn his suggestion into a script which creates an instantly planned DBCC CHECKDB solution. Below I’ll explain the script in more detail.



The script I wrote contains parameters which define how the DBCC check runs. These parameters are the only things you need to edit to make the script work and create the solution.

-- The database in which a table with information about the schedule will be stored
-- =============================================
USE [master] ---> Put the name of @MGNDatabaseName here!!!!!
DECLARE @MNGDatabaseName varchar(400)
SET @MNGDatabaseName = '' --DEFAULT = MASTER

If you have your own database on the instance in which you store your maintenance stored procs etc., you can put the name of your database in the USE statement & reset the @MNGDatabaseName to your management database. If you alter this, the script objects (stored proc & tables) will be created in your chosen database. Otherwise, they will be created in the master database.

-- =============================================
-- The database on which the DBCC check will be created
-- =============================================
DECLARE @DatabaseName varchar(400)

This parameter will define the database which has to be checked.

-- =============================================
-- Check the tables with indexes(Yes) or without indexes (No)
-- =============================================
DECLARE @WithIndex varchar(10)
SET @WithIndex = 'No' -- Yes/No

You can choose to check your tables with index or without index.

-- =============================================
-- DBCC CHECK Physical Only?
-- Check the tables Physical Only?
-- =============================================
DECLARE @WithPhysical varchar(10)
SET @WithPhysical = 'Yes' -- Yes/No

To speed up your CHECKTABLE statement you can choose to only do a physical check of your table.

-- =============================================
-- =============================================
DECLARE @StartHour int
SET @StartHour = 0 -- DEFAULT START TIME = 00:00:00
-- Example: SET @StartHour = 22 --> START TIME = 22:00:00

This will define the start time of the SQL jobs that will be created. The CHECKTABLE job will start an hour after the start hour, so that CHECKALLOC and CHECKCATALOG don’t start at the same time as CHECKTABLE.

-- =============================================
-- Specify Day to execute CHECKCatalog
-- =============================================
DECLARE @CatalogDays varchar(250)
SET @CatalogDays = '' --DEFAULT = SUNDAY
-- Example: SET @CatalogDays = 'MONDAY,FRIDAY' --> ONLY Comma Separated !!!!

With this parameter you can define on which day(s) you want to perform a CHECKCATALOG on your database.

-- =============================================
-- Specify Days to execute CHECKALLOC
-- =============================================
DECLARE @AllocDays varchar(250)
-- Example: SET @AllocDays = 'MONDAY,FRIDAY' --> ONLY Comma Separated !!!!

Here you can define the days on which you want to perform a CHECKCATALOG on your destination database.

-- =============================================
-- Amount of days for 1 DBCC cycle
-- =============================================
DECLARE @AmountOfDaysForCheckDB int
SET @AmountOfDaysForCheckDB = 14 --DEFAULT = 7,
-- Possible options --> (1,2,3,4,5,6,7,8,9,10,11,12,13,14)

Here you can define in how many days you want to cycle through your database with your CHECKTABLE statements. In this way you can reduce the amount of tables being checked every day. After one cycle the job will start again with the first bucket. If the bucket wasn’t completed last time because of time issues, it will carry on from the last table it checked in the previous cycle. If all the tables were checked during the cycle, it will reset and start again from the first table.

-- =============================================
-- =============================================
DECLARE @Duration int
SET @Duration = 900 -- DEFAULT DURATION = 300
-- Example: SET @Duration = 300 (= 5 hours)

You can put a time limit on the bucket execution, so that after a certain amount of time no new statements are executed. The DBCC check will then continue with the remaining statements the next time it is bucket execution day.

After entering the parameters you can execute the script. This creates three jobs, a stored procedure and two tables.



1. DBCC Check Alloc + ’YourDatabaseName’
This job is created to check your database allocation with the DBCC CHECKALLOC command, and the schedule is defined by the parameters explained below.
2. DBCC Check Catalog + ’YourDatabaseName’
This job is created to check your database catalog with the DBCC CHECKCATALOG command, and the schedule is defined by the parameters explained below.
3. DBCC Check Daily Table + ’YourDatabaseName’
This job is created to execute DBCC CHECKTABLE statements for each of your buckets, and the schedule and frequency are defined by the parameters explained below.


Stored procedure

1 stored procedure is created:
1. [dbo].[DBCCTableCycle]
This stored procedure is used to execute the CHECKTABLE statements.



2 tables are created:
1. [dbo].[DBCCLoggingTable+’YourDatabaseName’]
This table contains some logging information on the progress of your DBCC check.
2. [dbo].[DBCCTableCheckup+’YourDatabaseName’]
This table contains the buckets with tables to be checked on certain days.


Job schedule

1. The DBCC Check Alloc job runs on the days you entered in the parameter @AllocDays. It also starts at the time you defined as the start hour for the job.
2. The DBCC Check Catalog job runs on the days you entered in the parameter @CatalogDays and starts at the time you defined as the start hour for the job.
3. The DBCC Check Daily Table job runs every day, over a maximum cycle of 14 days. The job starts one hour after your defined start time parameter (to ensure no conflict with the Alloc and Catalog jobs).


What if I made a mistake in my parameters

If you make a mistake you can just re-execute the script and alter your parameters. All existing jobs, tables and stored procs will then be deleted and recreated. If you change your Management Database or your Database Name, you will have to manually delete the objects.
The objects that you have to delete are:
1. Tables: [ManagementDatabase].[dbo].[DBCCLoggingTable+’YourDatabaseName’], [ManagementDatabase].[dbo].[DBCCTableCheckup+’YourDatabaseName’].
2. Jobs: DBCC Check Alloc ‘DatabaseName’, DBCC Check Catalog ‘DatabaseName’ and DBCC Check Daily Table ‘DatabaseName’.
3. Stored procedure: [ManagementDatabase].[dbo].[DBCCTableCycle].


What about high availability groups?

If you have high availability groups, you just have to execute the script on every node of your availability group. It will detect when it is the active node and continue the DBCC check on the new active side. If you have enabled ad hoc queries on your SQL Server instance and the SPN defined for your user, the DBCCTableCheckup’YourDatabaseName’ table will be filled with the data of your active node. If you do not have this configured, the table will be filled the next time your node is active.


I have had a lot of table changes in my database

The script will daily check for new tables and add them to a corresponding bucket. It will also look for deleted tables and delete them from their corresponding bucket.

I hope you enjoy the script. If you have any suggestions or any bugs to report, please don’t hesitate to contact me. In the next post I will explain my index maintenance strategy for a VLDB. Thanks for reading!

Stay tuned!

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...