What is a Good Way to Run CheckDB on a VLDB?

Introduction

Today’s script is one that I wrote based on the logic outlined in this post by Paul Randal (Blog|Twitter). This script is written for SQL 2000 but, as Paul notes, the logic will work on SQL 2005.

The Script

This stored procedure stays pretty true to the logic outlined in Paul’s post so I will just cover the differences here. The first thing to notice is that the parameters passed into the procedure are the days of the week that different portions of the check should run, the maximum run time in minutes and whether or not to print debug messages. The stored procedure then parses the input strings and runs CHECKALLOCs and CHECKCATALOGs if requested.

If tables should be checked today a little more work is necessary. I decided to use a utility database to hold work tables for my custom scripts called DBADB. The first part of performing a table check is to see if a work table already exists in the database. If the table does not exist then one is created and loaded with a list of all tables in the database. After the table is loaded, the process begins looping through the table, checking that the run time has not been exceeded then running checktable on each table. This continues until the table list has been processed or time runs out. If time runs out then the process picks up where it left off next time the table check starts to make sure all tables are eventually checked before starting over again.

Here is the script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
CREATE PROCEDURE [dbo].[sp_dba_checkdb_vldb] @days_to_run_checkalloc varchar(15) = '1,4', @days_to_run_checkcatalog varchar(15) = '1', @days_to_run_checktable varchar(15) = '1,2,3,4,5,6,7', @max_minutes_to_run int = 360, @debug_flag bit = 0
AS

 BEGIN

    DECLARE @date_part_search_string    char(3),
            @start_time                 datetime,
            @sql_text                   nvarchar(4000),
            @current_object_id          int

    SELECT  @date_part_search_string = '%' + CAST(DATEPART(dw, GETDATE()) AS VARCHAR) + '%',
            @start_time = GETDATE()

    IF PATINDEX(@date_part_search_string, @days_to_run_checkalloc) > 0
     BEGIN
        IF @debug_flag = 1
            PRINT 'DEBUG: Running DBCC CHECKALLOC'
            DBCC CHECKALLOC
     END

    IF PATINDEX(@date_part_search_string, @days_to_run_checkcatalog) > 0
     BEGIN
        IF @debug_flag = 1
            PRINT 'DEBUG: Running DBCC CHECKCATALOG'
            DBCC CHECKCATALOG
     END
     
    IF PATINDEX(@date_part_search_string, @days_to_run_checktable) > 0
     BEGIN
        DECLARE @control_table  varchar(500)
        SELECT  @control_table = DB_NAME() + '_dbcc_checktable_worklist'
        IF NOT EXISTS(SELECT * FROM [DBADB].[dbo].[sysobjects] WHERE name = @control_table)
         BEGIN
            SELECT  @sql_text = 'SELECT DISTINCT
                                        i.id,
                                        CAST(NULL AS datetime) AS run_date_time
                                INTO    [DBADB].[dbo].'
+ QUOTENAME(@control_table) + '
                                FROM    sysindexes i
                                        INNER JOIN sysobjects o
                                            ON i.id = o.id
                                WHERE   o.type != '
'TF'''

            IF @debug_flag = 1
                PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
            EXEC sp_executesql  @sql_text

            SELECT  @sql_text = '
CREATE CLUSTERED INDEX IX_' + @control_table + '_id_run_date_time ON [DBADB].[dbo].' + QUOTENAME(@control_table) + ' (id, run_date_time)'
            IF @debug_flag = 1
                PRINT '
DEBUG: Running sql command: [' + @sql_text + ']'
            EXEC sp_executesql  @sql_text
         END

        SELECT  @sql_text = '
  SELECT  TOP 1 @current_object_id = c.id
                                FROM    [DBADB].[dbo].[' + @control_table + '] c
                                        INNER JOIN sysobjects o
                                            ON c.id = o.id
                                WHERE   c.run_date_time IS NULL
                                            AND o.type != ''TF'''
         
        IF @debug_flag = 1
            PRINT '
DEBUG: Running sql command: [' + @sql_text + ']'
           
        EXEC sp_executesql @sql_text, N'
@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT
       
        IF @debug_flag = 1
            PRINT '
DEBUG: @current_object_id = ' + ISNULL(CAST(@current_object_id AS varchar), 'NULL')
       
        WHILE   @current_object_id IS NOT NULL AND DATEADD(mi, @max_minutes_to_run, @start_time) > GETDATE()
         BEGIN
            SELECT @current_object_id = NULL

            SELECT  @sql_text = '
  SELECT  TOP 1 @current_object_id = c.id
                                    FROM    [DBADB].[dbo].[' + @control_table + '] c
                                            INNER JOIN sysobjects o
                                                ON c.id = o.id
                                    WHERE   c.run_date_time IS NULL
                                                AND o.type != ''TF'''

            IF @debug_flag = 1
                PRINT '
DEBUG: Running sql command: [' + @sql_text + ']'
               
            EXEC sp_executesql @sql_text, N'
@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT

            IF @debug_flag = 1
                PRINT '
DEBUG: @current_object_id = ' + ISNULL(CAST(@current_object_id AS varchar), 'NULL')
               
            IF @debug_flag = 1
                PRINT '
DEBUG: Running DBCC CHECKTABLE(' + CAST(@current_object_id AS varchar) + ')'

            DBCC CHECKTABLE(@current_object_id)

            SELECT  @sql_text = '
  UPDATE  [DBADB].[dbo].[' + @control_table + ']
                                    SET     run_date_time = GETDATE()
                                    WHERE   id = @current_object_id'

            IF @debug_flag = 1
                PRINT '
DEBUG: Running sql command: [' + @sql_text + ']'

            EXEC sp_executesql @sql_text, N'
@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT
         END
       
        IF @current_object_id IS NULL
         BEGIN
            PRINT '
Ran out of work to do...cleaning up and shutting down.'
            IF EXISTS(SELECT * FROM [DBADB].[dbo].[sysobjects] WHERE name = @control_table)
             BEGIN
                SELECT  @sql_text = '
DROP TABLE [DBADB].[dbo].' + QUOTENAME(@control_table)
                IF @debug_flag = 1
                    PRINT '
DEBUG: Running sql command: [' + @sql_text + ']'
                EXEC sp_executesql  @sql_text
             END
         END
        ELSE
            PRINT '
Ran out of time...shutting down.'
     END
 END
GO

Conclusion

As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

2 thoughts on “What is a Good Way to Run CheckDB on a VLDB?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.