DBCC- Database Consistency Checker
The DBCC is a set of utility command for checking of the logical abd physical consistency of a database.
Two major functions are :
*** Command that checks consistency of a entire database dbcc checkstorage
DBCC Commands
dbcc checktable
dbcc checkdb
dbcc checkcatalog
dbcc tablealloc
dbc indexalloc
dbcc checkalloc
dbcc checkstorage
dbcc checkverify
dbcc checktable
It Checks page linkage for a given data
It Checks indexes and data pages are correctly linked
It Checks indexes are in properly sorted order
It Checks for the consistency of indexes and data pages
Syntax : dbcc checktable <table name>
Example :
If we want to checks to be done first we need to skip the non-clustered indexes
Syntax : dbcc checktable <table name> skip_ncindex
Example :
To fix the errors and its default
Syntax : dbcc checktable tablename with fix
Example :
This Generates the reports
Syntax : dbcc checktable tablename with nofix
Example :
dbcc checkdb
If this command is issued internally it checks table
It checks all tables in a given database
If no database is specified the check is performed on the current database
To run this command db should be in “SINGLE USER MODE” I.e set db_option
Syntax : dbcc checkdb <database name> {Default is with fix}
Example : dbcc checkdb titles
dbcc checkcatalog
Checks for the referential integrity between the system table in the database
Verifies that the table or view in the sysobjects tables has at-least one row in syscolumns tables
Verifies that the type in the syscolumns tables has a row in systypes table.
Checks that the last checkpoint in the syslogs table is valid
Checks the segments definition in syssegments table
Verifies that the procedure in the sysobjects table has atleast one row in sysprocedures table
Also checks sysdevices and sysusages table also I.e whether all the devices are there or not
Syntax : dbcc checkcatalog <database name > {doesnt have options like nofix and skip nc_index}
Example :
dbcc tablealloc
Checks for datatypes and index pages
Checks whether the pages are properly allocated in the table and in the index of a particular table
Checks whether pages that are allocated are properly linked with each other
It ensures that no allocated page is linked with the page of that particular table
Syntax : dbcc tablealloc(object_name | object_id | partition_id,
[, {full | optimized | fast |null}
[, fix | nofix]])
Example : dbcc tablealloc (titles,optimized)
optimized : Generates reports based on OAM Table
full : Generates the full report
null : By Default takes optimized
fast : fast creates an exemption and like a reference pointer of next apge is there and the particular page doesn't exist
{Combination of 8 pages is 1 extent}
fix : If we give tablealloc for system tables by default it runs with the option and its
default for them
nofix : For user tables default is nofix
** sp_spaceused <table name> gives how much memory is used ,reserved , size of the index and no. of rows in a table
dbc indexalloc
Syntax : dbcc indexalloc(object_name | object_id | partition_id, index_id
[, {full | optimized | fast | null} [, fix | nofix]])
Example :dbcc indexalloc (authors,full)
*** sp_helpindex <object name> → gives the information for that particular table
dbcc checkalloc
When runs on a particular db it internally runs tablealloc on each and every table in that particular database .it database name is not mentioned it checks the present database and database should be in SINGLE USER MODE
Syntax : dbcc checkalloc [(database_name [, fix | nofix] )]
Example : dbcc checkalloc pubs2,fix
For better performance
use dbcc checkalloc to locate error (but do not fix them)
use dbcc tablealloc as needed to fix errors
Default dbcc checkalloc is no fix ,to use it with fix option,the database must be in SINGEL USER MODE
Dropping a Damaged Database
If a database has large enough number of inconsistencies the server may consider the database to be corrupt and mark it suspect
In this situation the database cannot be dropped with the drop database command
The database can be dropped only with dbcc dbrepair
Syntax : dbcc dbrepair(database_name, dropdb)
Example : dbcc dbrepair(database_name, dropdb)
dbcc checkstorage
Syntax : dbcc checkstorage [(dbname)]
Example : dbcc checkstorage pubs2
In checktable it only checks for datapages
checkdb → checks for dataapge
dbcc checkstorage just generates a report and doesn't fix the error
when dbcc checkstorage command is run on a db it puts that db in dbccdb and performs all other dbcc commands
The faults that encountered in dbcc checkstorage will be stored in dbcc_faults
whenever dbcc commands are run on user database first dbcc needs to installed and this database can be on production server/development
Advantages
Doesn't lock tables or pages for extended period of time
Executes in parallel using multiple worker process
Logs integrity faults in a database instead of standard output
Scales nearly linearly as resources are added
dbcc checkstorage checks the entire database including unused pages
Stores checkstorage activity and results in dbccdb database
Check storage only detects the faults it will not repair we need to run appropriate dbcc command to repair the faults
workspace is special type of table pre-allocated for dbcc checkstorage
A scan work space which contains a row for each page of target database
A text namespace contains a row for each table in target database that contain a text or image column
dbcc checkverify
dbcc checkverify reads faults recorded by check storage
Sybase recommends that to run a check verify as soon as possible after running check storage
dbcc checkverify locks against concurrent updates to reclassify soft faults corrupted
dbcc checkverify reads information in dba_operation and dba_operation_results
code 100011 → it is a HARD FAULT & TEXT POINTER FAULT can be resolved without recovering database or dropping the table
code 100016 → page allocated but not linked (depends upon page allocation or database allocation then we need to run a tablealloc or checkalloc)
code 100035 → Space bits mismatch (To fix this run dbcc checktable)
Syntax : dbcc checkverify <dbname>
or
sp_dbcc_runcheck
Example :
The faults that are encountered in dbcc checkstorage are stored in dbcc_faults
Two Types of Faults:
Soft Fault :
These are not persistent and can be rectified by running dbcc commands.
code 100020 → Check aborted
code 100025 → Row count Fault
code 100028 → Page allocation of current segment
Soft faults are those faults whenever there is page linkage error for
Example : If a page is pointing to the next page and that page doesn't exist such faults are
called soft faults
During the time of data modification are happening on the pages and at the same timethe dbcc checkstorage is done it reports for soft faults
These soft faults may not be 100% faults reported .In order to verify whether these are the actual faults we need to run dbcc checkverify which verifies the soft faults reported by previous dbcc checkstorage and gives the actual report
Hard Fault:
These are persistent errors and by restarting also they will not get resolved
Only one way to resolve is recover the database from backup
Even after re-running the command to resolve soft faults and if they do not get resolved they are said to be hard faults
When ever dbcc checkstorage is run in SINGLE USER MODE. We can encounter less no of soft faults which leads to outage i.e. the downtime.
Classifies the fault. Valid values are:
0 – Soft fault, possibly transient
1– Hard fault
2 – Soft fault that proved to be transient
3 – Soft fault upgraded to a hard fault
5 – Repaired hard fault
7 – Repaired upgraded hard fault
9 – Hard fault not repairable
11 – Soft fault upgraded to a hard fault and not repairable
16 – Soft fault, object dropped (inaccessible)
17 – Hard fault, object dropped (inaccessible)
18 – Transient soft fault, object dropped (inaccessible)
19 – Soft fault upgraded to a hard fault and object dropped (inaccessible)
dbcc reports
dbcc checktype generated four types of reports and are stored in dbccdb database
sp_dbcc_summaryreport It gives the summary of the HARD Fault and SOFT Fault that are reported
Example : sp_dbcc_summary_report [dbname,(,date)(,opname)]
sp_dbcc_faultreport It displays detailed information for each fault report
sp_dbcc_faultreport 'short'
sp_dbcc_faultreport 'long' {sp_dbcc_summaryreport / sp_dbcc_configreport / sp_dbcc_statisticsreport}
sp_dbcc_fullreport Displays configuration statistics and fault data for a given database or object
sp_dbcc_differentialreport Displays comperative results of the dbcc_checkstorage operations that are completed for a specified dates
Maintaining dbccdb
sp_dbcc_deletedb It deltes all the information on the specified database from dbccdb
sp_dbcc_deletehistory It deletes the results of dbcc checkstorage operations from dbccdb
*** Difference between checkstorage and checkdb ?
dbccdb must be installed to run checkstorage the results will be stored in the database.
If we use checkdb it will lock the database while running checkdb
If we use checkstorage we should install the dbccdb because it will copy the source database to dbcc
*** How dbcc checkstorage works ?
Initialization Verifies the configuration of the target database and availability of required resources
Database Scan Reads the entire database as quickly as possibly ,performs some checks and writes a brief summary of every page to the workspace in dbccdb
Remaining checks are performed entirely in dbccdb
*** How do you know the size of dbccdb ?
sp_plan_dbccdb<dbanme>
If dbaname is not specified sp_plan_dbccdb makes recommendations for all databases in master sysdatabases
dbcc errors To make scanning dbcc output easier use operating system scripts to scan the output to look for specific strings
String to look for:
Msg → All Error Msg start with Msg
Error → This word is often found in error messages
Severity → Error that have a severity greater than 16 are serious
Error Messages between 2500 & 2599 with a severity level of 16 can be a corrupt object
1- 16 → LOW
16-20 → To be Done
21- Last → Taken to Technical Support /Senior administrator
