Maintenance Tasks – DBCC

DBCC- Database Consistency Checker

The DBCC is a set of utility command for checking of the logical abd physical consistency of a database.

• User id's that have been granted the SA_ROLE or the DBO role are automatically granted permissions to use all the dbcc commands.
• Before a database dump or a routine validation of a database use
  • dbcc checkstorage
  • dbcc checkverify

Two major functions are :

• Page linkage
  • Commands to check the page linkage
    • dbcc checktable
    • dbcc checkdb
    • dbcc checkcatalog

• Page Allocation
  • Commands to check page allocation
    • dbcc tablealloc
    • dbc indexalloc
    • dbcc checkalloc

*** 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

• Checks for only index pages and remaining checks are same as table alloc
• Options are same as tablealloc

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

• In order to run dbcc checkstorage we need to install dbcc database
• It is special dbcc command that combines the functionality of tall the commands like
  • dbcc checktable
  • dbcc checkdb
  • dbcc checkalloc
  • dbcc indexalloc
  • dbcc tablealloc

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
• Hard Fault

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

1. 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)]

2. 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}

3. sp_dbcc_fullreport Displays configuration statistics and fault data for a given database or object

4. 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 ?

1. dbccdb must be installed to run checkstorage the results will be stored in the database.
2. If we use checkdb it will lock the database while running checkdb
3. If we use checkstorage we should install the dbccdb because it will copy the source database to dbcc

*** How dbcc checkstorage works ?

1. Initialization Verifies the configuration of the target database and availability of required resources
2. 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
3. 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