Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating