RYO Maintenance Plan – Integrity Checks
Happy Monday! Everyone fully recovered from St. Patrick’s Day? Hopefully the weather where you are was as perfect as it was here in Cleveland.
Today’s piece of RYO Maintenance Plan deals with database integrity checks; a critical component second only to backups in my book.В The procedure I’ve written is really just a wrapper for the DBCC CHECKDB command. But before I jump into the code, let’s take a moment and talk about what exactly DBCC CHECKDB does. In a nutshell, CHECKDB checks both the logical and physical integrity of all of the objects in your database. It accomplishes this by stepping through a series of lower-level checks.
- DBCC CHECKALLOC validates the consistency of the internal allocation structures that keep track of your database pages, i.e. IAM, GAM/SGAM, and PFS pages.
- DBCC CHECKTABLE checks the logical and physical integrity of individual tables. It validates that every row in a table has a corresponding record in any non-clustered index on the table, and, inversely, that all index records have a row in the table. Index sort order is verified, partitions are checked to ensure they contain the correct rows, data is examined to ensure it is within the acceptable range for its data type. On a physical level, data pages are checked to verify they’re linked properly.
- DBCC CHECKCATALOG examines the data inside the system catalog tables, verifying the consistency in and between the system tables.
- Check Service Broker objects and validate the relationships between them. A service has to be assigned to a valid queue and contract, etc.
- Validate the relational engine metadata in the system catalog.
- Verify that all indexed views contain the correct data.
What you may not know is that SQL Server requires a transactionally consistent view of the database in order to reliably perform these checks. To achieve this, SQL Server actually creates a hidden snapshot of your database and runs the checks against that snapshot. This snapshot is created inline with your database datafile(s). As you’ll recall, snapshots use sparse files that start out small but grow as data is changed in your source database. If you’re low on disk space and are running CHECKDB on a busy system, you run the risk of possibly filling up your data drive. If you’re concerned about this, you can always manually create a snapshot, placing the files in a better location, and run CHECKDB against that snapshot. It will work just as well.
Ok, so now we know a little bit more about what DBCC CHECKDB is doing, let’s take a look at the procedure I’m using. It’s so darn simple I won’t insult your intelligence by stepping through it. The only options I included were a parameter to specify the target database (just like the previous procedures) and a flag to specify whether or not to display informational messages. The default is ‘N’, to suppress these messages (my personal preference).
USE [master] GO /****** Object: StoredProcedure [maint].[sp_checkdb] Script Date: 02/23/2012 15:54:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [maint].[sp_checkdb] (@dbs VARCHAR(128) = '%', @infomsgs char(1) = 'N') AS BEGIN DECLARE @tablename varchar(128) ,@schemaname varchar(128) ,@indexname varchar(128) ,@dbname varchar(128) ,@dbid smallint ,@sqlstmt nvarchar(max) SET NOCOUNT ON BEGIN TRY IF @infomsgs NOT IN ('Y', 'N') RAISERROR ( N'Invalid value for parameter @infomsgs. Please specify Y (display informational msgs) or N (do not display; i.e. NO_INFOMSGS).' ,16 ,1 ); --loop through all the databases we're covering DECLARE getdbs CURSOR FOR SELECT d.database_id,d.name FROM sys.databases d WHERE d.name <> 'tempdb' AND d.state = 0 AND d.name LIKE @dbs OPEN getdbs FETCH getdbs INTO @dbid, @dbname WHILE @@FETCH_STATUS = 0 BEGIN PRINT convert(varchar, getdate(),120)+' Beginning checkdb for database: '+@dbname --build the dbcc statement SET @sqlstmt = 'USE '+quotename(@dbname, '[')+'; DBCC CHECKDB (N'''+@dbname+''') ' --get only changed objects IF @infomsgs = 'N' SET @sqlstmt = @sqlstmt + 'WITH NO_INFOMSGS ' EXEC (@sqlstmt) --PRINT @sqlstmt FETCH getdbs INTO @dbid, @dbname END CLOSE getdbs DEALLOCATE getdbs PRINT convert(varchar, getdate(),120)+' Complete' END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return -- error information about the original error that -- caused execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH END
And that’s all it is. You’ll notice I added some PRINT statements in there, which, in the event of any errors, will help determine what database they originated from.
For more information
For aВ really in-depth look at CHECKDB, I encourage you to mosey on over to Paul Randal’s blog. Just do it when you have a lot of free time, you can lose hours immersed in all that knowledge.