At the company I’m working at the moment they had a problem with the maintenance of a few monsters of databases(VLDB). They had about 2-4 TB of data and more than 90000 tables. Building a good maintenance solution for such databases is not an easy thing to do. For most of my clients I use the ola hallengren solution which has proved to be an excellent solution but here it just wouldn’t fit.
The first thing on my list was getting these databases checked, the normal DBCC CHECKDB command would go on for days until its snapshot would clog up my server, the with physical only option did not help. Checking all tables separately took way too much time.
So I decided to do some reading online and found an answer in Paul Randall’s blog http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/ . Today’s post will contain the way I chose to implement his suggestion.
The basic concept of Paul’s blog is that you chop up your CHECKDB into CHECKTABLE CHECKALLOC & CHECKCATALOG. He suggested doing this on a weekly basis. So 7 days of Checktables & 2 days of CHECK ALLOC & CHECKCATALOG. Since I was going to have to do this for a lot of databases I chose to make a script of his suggestion.
The script I wrote can be found in the following link: https://www.dropbox.com/s/d5lvufbw7wyqbgi/BigDbccCheck.sql?dl=0
This script will automatically create a DBCC checkDB solution, which will be instantly planned as well.
In the next part of this blog I will be explaining the script in more detail.
The Script contains a few parameters which will define how the DBCC check will run. These parameters are the only things you have 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 these 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 chose 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 starthour, to avoid starting CHECK ALLOC & CHECK CATALOG at the same time as the 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 Seperated !!!!
With this parameter you can define on which day(s) you want to perform a catalog check of you 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 Seperated !!!!
Here you can define the days on which you want to perform a CHECK CATALOG 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 done every day. After one cycle the job will then start again with the first bucket. If the bucket did not finish last time because of time issues it will continue from the last table it checked the previous cycle. If all the tables have been checked for a 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. After a certain amount of time no new statements will be executed. The DBCC check will continue with the remaining statements the next time it is the bucket execution day.
After you entered the parameters you can execute the script, the script will then proceed to create 3 jobs, a stored procedure and 2 tables.
3 Jobs will be created
- DBCC Check Alloc +’YourDatabaseName’
- This job will be created to check your database allocation with the DBCC CHECK ALLOC command, schedule will be defined by parameters which will be explained in the following paragraph
- DBCC Check Catalog +’YourDatabaseName’
- This job will be created to check your database catalog with the DBCC CHECK CATALOG command, the schedule will be defined by parameters which will be explained in the following paragraph
- DBCC Check Daily Table +’YourDatabaseName’
- This job will be created to execute DBCC CHECKTABLE statements for each of your bucket, the schedule and frequency will be defined by parameters which will be explained in the follow paragraph
1 Stored Procedure will be created
- This stored procedure will be used to execute the CHECKTABLE statements.
2 Tables will be created
- This table will contain some logging information on the progress of your DBCC check
- This table will contain the buckets with tables that will be checked on which day
- The DBCC Check Alloc job will be run on the days you entered in the parameter @AllocDays. It will also start on the hour you defined as the starthour for the job.
- The DBCC Check Catalog job will also be run on the days you entered in the parameter @CatalogDays, and it will start on the hour you defined as the starthour of the job
- The DBCC Check Daily Table job will run every day, in a maximum cycle of fourteen days. The job will execute one hour after your defined starttime parameter. (To ensure no conflict between the Alloc & Catalog jobs)
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 & stored procs will 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 will have to delete are
- Tables : [ManagementDatabase].[dbo].[DBCCLoggingTable+’YourDatabaseName’], [ManagementDatabase].[dbo].[DBCCTableCheckup+’YourDatabaseName’].
- Jobs : DBCC Check Alloc ‘DatabaseName’, DBCC Check Catalog ‘DatabaseName’ and DBCC Check Daily Table ‘DatabaseName’.
- 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 fill 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 check daily for new tables and add them to a corresponding bucket, it will also look for deleted table and delete them from their corresponding bucket.
I hope you enjoy the script, if you have any suggestions or have any bugs to report, pls don’t hesitate to contact me.
In the next post I will be explaining my Index Maintenance strategy for a VLDB. Thanks for reading!