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.
