Previously, you learned about the SQL Server command called database consistence checker (dbcc) that can be used to monitor
SQL Server’s database, ensuring that SQL Server’s internal structures are valid.
The next three lessons will show you nine different ways to use dbcc to monitor and maintain SQL Server databases, including:
dbcc checkalloc – Checks the data pages allocated to tables and indexes
dbcc checkcatalog – Verifies SQL Server system tables are correct
dbcc checkdb – Checks the data pages allocated to all tables and indexes and checks structural integrity of the database
dbcc checkfilegroup – Validates the data in a file group
dbcc checkident – Validates a tables identity column
dbcc checktable – Checks the data pages allocated to tables and indexes
dbcc show_contig – Displays fragmentation information about a table
dbcc dbreindex – Rebuilds the indexes on a table
In this lesson, we will begin by looking at dbcc checkalloc, dbcc checkdb, and dbcc checkcatalog.
dbcc checkalloc
The dbcc checkalloc command is used to check that all of the pages in the database are correctly assigned.
If there are any errors, you can also use this command to fix the database.
The syntax is as follows:
The name of the database to check.
Causes dbcc checkalloc to skip checking non-clustered indexes.
Performs more complex repairs that can result in data being lost.
Performs minor fast repairs that can be done without any data lost
Besides the minor repairs, it can perform more complex repairs that will not result in data loss.
Causes dbcc checkalloc will display all error messages.
Causes dbcc checkalloc display no informational messages. When using this option, you will only see messages if there are errors.
Dbcc checkalloc will validate every table and every data page in the database, and will
display information about every table and will
provide messages about errors it finds. If you do not specify either ALL_ERRORMSGS or NO_INFOMSGS, it will display up to 200 errors per table.
If you are going to use dbcc checkalloc to fix errors, you need to have the database in single user mode. Single user mode: A database that is configured to allow only one user to access it.
dbcc checkdb
Like dbcc checkalloc, the dbcc checkdb command is used to check that all of the pages in the database are
correctly assigned. If there are any errors, it can be used to fix the database. Notice that the optional parameters are the same
as those in dbcc checkalloc.
The procedure dbcc checkdb can repair everything the dbcc checkalloc can, plus it can perform more detailed checks. dbcc checkdb will validate the pages allocated to every table page allocation, and it will check the following, which are not checked by dbcc checkalloc:
Index and data pages pointers are correct.
Indexes are stored in the proper (sorted) order.
The data on each page is reasonable.
Page offsets are reasonable.
dbcc checkdb is the perferred method over dbcc checkalloc. It does a much more thorough job.
By default, it will report on every table in the database.
dbcc checkcatalog
The dbcc checkcatalog command is used to verify the consistency of SQL Server system tables.
It verifies syscolumns, systypes, and sysviews. The syntax is as follows:
DBCC CHECKCATALOG ( 'database_name') [WITH NO_INFOMSGS]
If there are no errors, dbcc checkcatalog will not return any messages. Dbcc checkcatalog will only report onerrors, it does not fix any errors.
In the next lesson, you will continue your exploration of the dbcc command and learn how to use it to validate individual tables.