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.

1. 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.
2. 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.
3. DBCC CHECKCATALOG examines the data inside the system catalog tables, verifying the consistency in and between the system tables.
4. Check Service Broker objects and validate the relationships between them. A service has to be assigned to a valid queue and contract, etc.
5. Validate the relational engine metadata in the system catalog.
6. 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.