12.1. DBCC validation overview

Table 12.1 shows the full set of DBCC commands by category. Note that there are a number of undocumented commands not found in this list, such as DBCC PAGE, which we'll cover later in the chapter.

Table 12.1. DBCC commands by category
 

DBCC category	DBCC statement
Informational	DBCC INPUTBUFFER
	DBCC SHOWCONTIG
	DBCC OPENTRAN
	DBCC SQLPERF
	DBCC OUTPUTBUFFER
	DBCC TRACESTATUS
	DBCC PROCCACHE
	DBCC USEROPTIONS
	DBCC SHOW_STATISTICS
Validation	DBCC CHECKALLOC
	DBCC CHECKFILEGROUP
	DBCC CHECKCATALOG
	DBCC CHECKIDENT
	DBCC CHECKDB
Maintenance	DBCC CLEANTABLE
	DBCC INDEXDEFRAG
	DBCC DBREINDEX
	DBCC SHRINKDATABASE
	DBCC DROPCLEANBUFFERS
	DBCC SHRINKFILE
	DBCC FREEPROCCACHE
	DBCC UPDATEUSAGE
Miscellaneous	DBCC DLLNAME
	DBCC HELP
	DBCC TRACEON
	DBCC TRACEOFF

You can find the full description of each of these commands in SQL Server Books Online (BOL). In the next chapter we'll cover some of the maintenance and informational commands along with their preferred replacements. For now, our focus is on the validation commands.

The DBCC validation commands are primarily concerned with checking the logical and physical integrity of database objects. Figure 12.1 highlights the relationships among these commands.

We'll cover what each of these commands do shortly, but for now it's important to point out that running DBCC CHECKDB automatically runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG. Running these commands individually (instead of DBCC CHECKDB) is useful in some situations, such as when you want to reduce the runtime on large databases, an important technique we'll cover later in the section "Controlling CHECKDB impact."

Let's walk through these commands now, beginning with DBCC CHECKDB.

12.1.1. DBCC CHECKDB

DBCC CHECKDB is the most commonly used validation command for checking the logical and physical integrity of the entire database. Among other things, DBCC CHECKDB

• Runs DBCC CHECKALLOC on the specified database. CHECKALLOC validates the correct allocation of each page within the database.

• Runs DBCC CHECKTABLE on each table and indexed view in the specified database. CHECKTABLE runs a comprehensive series of tests, including checking that each row in the base table has a corresponding row in each nonclustered index.

• Runs DBCC CHECKCATALOG. In SQL Server 2000 and earlier, this command had to be run in addition to DBCC CHECKDB. Now included as part of CHECKDB, CHECKCATALOG checks database metadata consistency.

• If you're using FileStream, CHECKDB validates the links between table metadata and the appropriate files and directories containing the FileStream data.

• Validates indexed views and service broker data.

Looking through these actions, you'll note that one of the things CHECKDB does is confirm that all base table rows have matching nonclustered index rows (covered in more detail in the next chapter). DBCC CHECKDB is an online operation, so one of the challenges for CHECKDB is running against a transactionally consistent view of the database.

Transactional consistency

In prior versions of SQL Server, a number of techniques were used by DBCC to obtain a transactionally consistent view of the database—approaches such as including table locks and using the transaction log to catch up with transactions that occurred during the check. In some cases, these techniques resulted in substantial blocking, and therefore performance reduction, or complex checking logic that sometimes led to false positives, requiring additional CHECKDB runs to confirm any reported inconsistencies. Such limitations caused some sites to either exclude CHECKDB from their maintenance routines, or run it infrequently, a particularly dangerous move for large, mission-critical databases.

In SQL Server 2005, a significant breakthrough was made in obtaining a transactionally consistent view of the database without the performance impact of table locks or false positives. By leveraging the database snapshot technology (covered in chapter 10), CHECKDB runs without requiring table locks or transaction log analysis. The database is read through a special system-generated hidden snapshot ensuring that the before image of any concurrent activity is made available to CHECKDB.

One of the downsides of using a system snapshot is the lack of control over its placement. If CHECKDB is run during periods of high activity, the snapshot may grow very large, and in some cases, potentially consume all available disk space. We'll cover this case a little later when we look at running CHECKDB against a user-defined snapshot, which gives you control over the placement of the snapshot in a specific disk location.

Despite the significant advances made with snapshots, the performance impact of running CHECKDB operations may still exceed the desired level, particularly for large, 24/7 databases with a high transaction load. Given the importance of what CHECKDB delivers, there are a number of techniques that can be used to reduce the impact, and we'll cover these later in the chapter. For now, let's look at the syntax and options of the CHECKDB command.

Syntax and options

The full syntax of the DBCC CHECKDB command is as follows:

DBCC CHECKDB
[
    [ ( database_name | database_id | 0
        [ , NOINDEX

[ , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
        ) ]
    [ WITH
         {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ]
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
         }
    ]
]

Despite the large amount of options, DBCC CHECKDB can be run on its own as well. Here's a brief description of these options:

• database_name/id—Providing no value for this option runs the check against the current database; otherwise, the checks are run against the specified database.

• NOINDEX—When specified, nonclustered indexes aren't checked, reducing overall execution time. We'll cover clustered and nonclustered indexes in the next chapter.

• REPAIR options—If corruption is found, and no appropriate backups exist (a worst-case scenario), the repair options can be used as a last resort in removing database corruption. We'll cover the implications and details of these options later in the chapter.

• ALL_ERRORMSGS—If this option is excluded, CHECKDB displays only the first 200 errors for each object. Even if this option is included, SQL Server Management Studio displays the first 1,000 errors only. So for a complete list of errors, run CHECKDB with this option using either the sqlCmd utility or as a SQL Agent job with output directed to a file for later analysis.

• EXTENDED_LOGICAL_CHECKS—When run against SQL Server 2008 databases (compatibility level 100), this option will perform logical consistency checks against spatial and XML indexes as well as indexed views.

• NO_INFOMSGS—This option excludes informational messages from the output. When executed with this option, a successful CHECKDB will simply return Command(s) completed successfully; otherwise, messages will be returned for each object such as There are 19118 rows in 187 pages for object "Sales.CreditCard".

• TABLOCK—When executed with this option, CHECKDB will use table locks rather than an internal snapshot. For databases under heavy transaction load, this option usually results in a faster execution time at the expense of lower concurrency. Note that this option will preclude service broker and CHECKCATALOG checks from being run.

• ESTIMATE_ONLY—This option estimates the amount of tempdb space required to run the DBCC command (without actually running it) and can be used to ensure the tempdb database is large enough and/or has access to enough free disk space for the CHECKDB operation.

• PHYSICAL_ONLY—A full execution of CHECKDB, including all logical checks, may take a considerable amount of time, given the extensive checks involved. Using this option reduces the execution time while still checking important aspects of the database integrity. We'll cover this option in more detail later in the chapter.

• DATA_PURITY—When a database is upgraded from SQL Server 2000 or earlier, DBCC CHECKDB won't check column value integrity until CHECKDB is successfully executed with this option.

So with these options in mind, let's take a look at running CHECKDB against the AdventureWorks database:

DBCC CHECKDB (AdventureWorks2008)

The abbreviated output of this command is as follows:

DBCC results for 'AdventureWorks'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1331 rows in 10 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 255 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'Sales.SpecialOfferProduct'.
There are 538 rows in 3 pages for object "Sales.SpecialOfferProduct".
DBCC results for 'Production.ProductModel'.
There are 128 rows in 12 pages for object "Production.ProductModel".
CHECKDB found 0 allocation errors and 0 consistency errors in database
 'AdventureWorks'.
DBCC execution completed. If DBCC printed error messages, contact your
 system administrator.

In this example, CHECKDB was executed without the NO_INFOMSGS option; therefore, we receive messages such as those shown here (the output has been truncated for brevity). The most important part of the output is toward the end: 0 allocation errors and 0 consistency errors. Later in the chapter we'll look at an example where DBCC returns errors and use that to discuss the recovery options.

Earlier in the chapter we discussed the fact that DBCC CHECKDB is a superset command that actually runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG. To understand what CHECKDB is doing under the covers, let's examine these commands a little further.

12.1.2. Granular consistency checking

As mentioned earlier, CHECKALLOC is included as part of CHECKDB, but can be run separately. CHECKALLOC validates that each page in the database has been allocated correctly. To understand how allocation works (and how it's verified by CHECKALLOC), we need to look at the space allocation process used in a database.

Space allocation with GAM And SGAM Pages

As covered earlier in the book, tables and indexes in a database are stored on one or more 8K pages. Pages are allocated to objects from extents, which are collections of eight pages, making each extent 64K. To reduce wasted space for small tables or indexes, the first eight pages of an object are allocated from mixed extents—that is, eight tables may be using one page each from a single mixed extent. Once a table or index reaches eight pages, additional space is allocated one extent (64K) at a time from uniform extents.

To keep track of what mixed and uniform extents are available for allocation, SQL Server uses special allocation maps: the global allocation map (GAM) and the shared global allocation map (SGAM). Both the GAM and SGAM map to database extents using one bit to represent each extent. The GAM maps to all extents, and the SGAM maps to mixed extents only. In both cases, a bit value of 1 represents an available extent, and a 0 value represents an allocated (unavailable) extent.

As shown in figure 12.2, the first two pages of each database file are reserved for the file header and page free space pages, which we'll discuss shortly. The GAM and SGAM are located on the third and fourth pages (pages 2 and 3), respectively. With each page containing some 64,000 bits (8,000 bytes) and each bit representing an extent (64K), it follows that each GAM and SGAM page can therefore map to about 4GB of space. For database files larger than this size, additional allocation maps are used in the file.

When SQL Server needs to allocate a uniform extent to an object, it searches the GAM for a bit value of 1 and sets it to 0. It then has an extent for allocation. To allocate a mixed extent, SQL Server finds a GAM bit of 0 with the corresponding SGAM bit of 1 (allocated extent with free pages available). Table 12.2 illustrates the bit map used by GAM and SGAM pages.

Table 12.2. GAM and SGAM bit settings
 

Extent status	GAM bit setting	SGAM bit setting
Free	1
Uniform extent (allocated)	0
Mixed extent with free pages	0	1
Full mixed extent	0	0

As you learned in chapter 9, one of the advantages of creating multiple files for a database is getting multiple allocation maps. This is particularly useful for the tempdb database, which typically spends a large percentage of time allocating and deallocating tables. With multiple files, multiple GAMs and SGAMs reduce the contention to these resources, which, for a single file database (less than 4GB) would contain only a single GAM and SGAM.

Page Free Space (PFS) pages record information about each page in the database file, including its allocation status and the amount of free space in the page. After an object is allocated an extent, PFS pages are used to record which pages within the extent are available for allocation in the object—for example, when inserting a row into a table.

Let's take a look now at the commands executed by CHECKDB that can also be executed individually.

DBCC CHECKALLOC

When CHECKALLOC runs, it checks the validity of page and extent allocation in the database as recorded by the GAMs and SGAMs. Like CHECKDB, the CHECKALLOC command takes options for suppressing informational messages, ignoring indexes, and estimating tempdb usage.

DBCC CHECKTABLE

Like CHECKALLOC, CHECKTABLE is included as part of CHECKDB but can be executed separately for an individual table. It also contains options for suppressing informational messages, ignoring indexes, and other options, as documented for CHECKDB earlier.

CHECKTABLE runs a series of checks for a specified table, including the following:

• Page linkage for all pages within the table

• Index sorting

• Pointer consistency

• Correct values for computed columns

• Matches between index and base table records

• Correct placement of records in a partitioned table

• Correct linkage for FileStream data

The command also checks that indexed view contents match the appropriate view definition.

DBCC CHECKCATALOG

Also run as part of CHECKDB (since SQL Server 2005), CHECKCATALOG checks the consistency of metadata between system tables.

Unlike inconsistencies reported from CHECKTABLE, there are no repair options for errors resulting from CHECKCATALOG; a database restore is the only option. If run separately from CHECKDB, the only option CHECKCATALOG takes is NO_INFOMSGS.

Before completing our overview of DBCC validation commands, let's take a look at three more we haven't covered yet.

12.1.3. Additional DBCC CHECK* commands

The following three CHECK* commands round out the DBCC validation category:

• CHECKFILEGROUP is similar to CHECKDB but is limited to objects in the specified filegroup. We'll cover this option in more detail a little later.

• CHECKIDENT is used to validate, correct, or re-seed the identity value for a table containing an identity column.

• CHECKCONSTRAINTS validates the integrity of a given table's foreign key and check constraints, and is useful for validating data entered while the constraint(s) were disabled.

While DBCC commands are unmistakably valuable in identifying (and in the worst-case scenario, removing) database corruption, what's equally important is ensuring databases remain corruption free. In the next section, we'll address some of the techniques useful in avoiding corruption.