How do I use DBCC ?

I am having issues with my Pharos Database and I would like to perform checks to make sure the database is in stable state, how do I perform these checks?

Why you need to use DBCC

If you are wondering why using DBCC is even necessary, here are the reasons:

• Database pages (both tables and indexes) need to be split from time to time, which can result in bad allocations.

• Indexes can become corrupted or simply inefficient.

• The SQL Server engine can occasionally misunderstand your intentions.

• In situations where a large number of updates is the norm, things can get complicated (remember that any given update is actually a delete plus an insert).

• Individual pages, while still "sound," may lose their optimal storage footprint.

How to run DBCC

You can run DBCC in two ways: from a command window and from inside a Query Analyzer window. You can also schedule its operations if you deem it necessary.

The DBCC command has the following extensions:

• CheckDB: checks the consistency of the entire database, and is the basic method to check for database corruption.

• CheckTable: checks a specified table for problems.

• CheckAlloc: checks the individual pages allocated to a database, both tables and indexes.

• Reindex: rebuilds the indexes on a specified table.

• CacheStats: tells you about the objects currently stored in the memory cache.

• DropCleanBuffers: drops all the data currently stored in the buffer, so that you can continue testing without using the previous results.

• Errorlog: erases (truncates) the current log. You might consider scheduling a job consisting of this command to run once a week or so.

• FlushProcInDB: clears out the stored procedure cache for the specified database (use its dbid not its name). Discover its id using:

SELECT dbid FROM master.dbo.sysdatabases WHERE name = '<name your poison>

• IndexDefrag: reduces fragmentation in indexes without imposing a lock on the files so that users can continue working with the database.

• CheckCatalog: checks the specified database for consistency in tables and between tables (the latter means foreign keys etc.).

Using DBCC CheckDB

This command ensures that:

• Data and index pages are correctly linked.

• Indexes are sorted correctly and are up to date.

• Pointers are consistent.

• Data on each page is up to date.

• Page offsets are up to date.

Here are three of the most common ways to use CheckDB:

DBCC CHECKDB ('pharos', REPAIR_FAST)
DBCC CHECKDB ('pharos', REPAIR_REBUILD)
DBCC CHECKDB ('pharos', REPAIR_ALLOW_DATA_LOSS)

There are several other options that you may specify, but these are the three critical options. The DBCC commands are presented in the order in which you should run them and then check the results afterwards. The first two options will not result in a loss of data, while the third will cause data loss. It's advisable to place the third command inside a transaction so you can perform a ROLLBACK if the data loss is unacceptable.

Using DBCC CheckTable

Any problems you encounter will most often be with one or more tables within a database rather than the entire database. In these cases, run DBCC CheckTable. First, use the database of interest and then run the DBCC CheckTable command. Here are two examples:

use pharos

DBCC CheckTable ('People')
DBCC CheckTable ('People', REPAIR_REBUILD)

Using DBCC CheckAlloc

This command checks the consistency of data pages and their indexes. Here are two examples:

DBCC CHECKALLOC ('pharos')
DBCC CHECKALLOC ('pharos', REPAIR_REBUILD)

Using DBCC CheckCatalog

Use this command to verify the consistency of a database's system tables. You specify the name of the database to check and optionally the argument WITH NO_INFOMSGS. Here is an example:

DBCC CHECKCATALOG ('pharos')

Using DBCC ReIndex

This command  perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance.

DBCC DBREINDEX('table_name', fillfactor)