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.

Parameters

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.

-- ENTER MANAGEMENT DATABASE NAME
-- 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.

-- =============================================
-- ENTER DATABASE NAME
-- The database on which the DBCC check will be created
-- =============================================
DECLARE @DatabaseName varchar(400)
SET @DatabaseName = 'MNGDB' ---ENTER NAME OF DATABASE

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

-- =============================================
-- DBCC CHECK WITH INDEX?
-- 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.

-- =============================================
-- ENTER JOB START HOUR
-- =============================================
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
-- Possible options --> (MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,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)
SET @AllocDays = '' --DEFAULT = SUNDAY & THURSDAY,
-- Possible options --> (MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY)
-- 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.

-- =============================================
-- ENTER MAX DURATION OF JOB (in minutes)
-- =============================================
DECLARE @Duration int
SET @Duration = 900 -- DEFAULT DURATION = 300
-- Example: SET @Duration = 300 (= 5 hours)
-->>>> IMPORTANT CHECK THE LOG TO SEE IF YOUR DBCC CHECKS ARE ALL COMPLETING OTHERWISE ALTER YOUR DURATION!!!!!!

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.

Jobs

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.

Tables

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!