SQL SERVER – DBCC CHECKDB Introduction and Explanation – DBCC CHECKDB Errors Solution

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB ran on database user should not run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG on database as DBCC CHECKDB includes all the three command. Usage of these included DBCC commands is listed below.

DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Along with above three DBCC commands it also runs following two tasks to check the validity database (physical as well logical) i.e. validates the contents of every indexed view in the database and Validates the Service Broker data in the database.

If database DBCC check has returned any errors, the best solution is to RESTORE DATABASE from BACKUP. (Additional reading : SQL Backup and Restore). There is additional keyword REPAIR with DBCC CHECKDB which can be used to repair database but it is not recommended. I will write additional articles on this subject.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , DBCC CHECKDB

About these ads

21 thoughts on “SQL SERVER – DBCC CHECKDB Introduction and Explanation – DBCC CHECKDB Errors Solution

  1. Hi Pinal!

    Can dbcc be run from a cmd prompt?

    How can it be run?

    sorry for my ignorance but I’m new to SQL.

    Thanks,

    Asher.

  2. Hi Pinal,
    We have a restore problem.

    We have SQL 2003 sp4 and suddenly no one can restore databases. Even newly created ones will not restore. We get the error msg “Could not open FCB for invalid file ID2 in database ‘databasename’
    The EVENTID is 17052 in Event Viewer. In there it says Error:5180, Severity:22, State:1″
    Any idea where I should look to fix this or how to fix it? Thanks, John

  3. Hi,

    To check the consistency of SQL 2000 and 2005, can we generate a DBCC report on both the databases and validate it line by line?

    Will that be the only possible solution?

    Or we got anything else?

    Appreciate any help!

    Thanks,
    Priya

  4. hi Pinal,

    Im an avid reader of your site and enjoying all your simple solution regarding sql.

    We have a problem in our company, our database (50gb) is always IN RECOVERY. And im always waiting for a 100% completion before we can use the program.

    Can you please enlighten me what is causing this, and what will i do to avoid such situation.I am worried that my live database will be corrupted totally and can no longer be recovered. I have a automatic back up but it is scheduled every end of the day, so my worries is the transactions within day. The worst scenario is backtrack all the transactions and its a desperate move i think lol. Kindly give me any advice that can resolve all my worries.

    Thank you so much and hoping to hear from you.

    More power!

  5. Hello Anne,

    Check the system hard disk/memory/processor utilization. Resolve the resource bottlenect if you find any.
    Allow the database to commit or rollback a transaction completely. Such problems occur usually when we cancel a long transaction and not wait to complete the rollback.
    If you found some length transaction then if possible try to break that in smaller transactions.
    If you are worried about the backup file’s correctness then add below one more statement in the backup job to verify the backup
    RESTORE VERIFYONLY FROM DISK = ‘backup file path’

    Schedule a transactional log backup in day time also so that you can fully recover the database if needed.

    Regards,
    Pinal Dave

  6. Hello,

    I would like to ask which permissions are needed on every possible maintenance task in SQL Server 2005 when running the jobs with a dedicated SQL LOGIN?

    Can anybody help.

    Many thanks in advance!

  7. Hi,

    Database has a corrupt sector, executing a query on one of the table throws “A possible database consistency problem has been detected on database
    ‘abcd’. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database ‘abcd’. Connection broken.”

    My VB.Net application is using this database and it exits without any error when it comes across the SQL operation on this table. Could you tell me is there an exception thrown from the SQL server or any way to handle this situation from the application.

    Regards,
    Mohan

  8. I am new to SQL server, is it possible to know what is written in the log file that is created with a database using DBCC commands. Or should i use a third party software. Can you help me out here…
    Thanks in advance..

  9. one of the Server migrated recently from one location to another location. at the time SQL task is….
    before migration need to take all db backup and run checkdb and update statastics..

    I ran before migration ..running everything fine..but after migration one of the database taking too long..the database size is 135GB…i checked there is no blockings..Could you please let me know what is causes it is taking too long run check db on database..
    Thanks
    Atcheswara

  10. Hi,

    This is chandara i am self learner sql server dba , in generally what are DBCC CHECKDB used in most of time.. and what situations using the those DBCC CHECKDB…

    could please give me the some important suggestions..

    Thanks,
    Chandra,

  11. hi,
    how to rectify the I/O error:823 torn page detected during read at an offset in mdf file.

    could you please give suggestions/solutions regarding this error.

  12. please give me reply, what does mean following error message- anyone help???

    SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbfc9c6fa; actual: 0x9fc9c6fa). It occurred during a read of page (1:425) in database ID 5 at offset 0×00000000352000 in file ‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HWDATA7.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  13. Pingback: SQL SERVER – Check for Database Integrity – Notes from the Field #011 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s