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

SQL
22 Comments

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 the database as DBCC CHECKDB includes all the three commands. Usage of these included DBCC commands is listed below.

SQL SERVER - DBCC CHECKDB Introduction and Explanation - DBCC CHECKDB Errors Solution dbcc-1-800x206

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

Solarwinds

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 the 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 (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – FIX : ERROR Msg 1803 The CREATE DATABASE statement failed. The primary file must be at least 2 MB to accommodate a copy of the model database
Next Post
SQLAuthority News – Microsoft SQL Server 2005 MSIT Three Configuration Pack for Configuration Manager 2007

Related Posts

22 Comments. Leave new

  • 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.

    Reply
  • how to execute DBCC command on linked server.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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!

    Reply
  • 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

    Reply
  • Pinal,

    Thank you for your help..I appreciate it so much…

    Anne

    Reply
  • Pinal,
    Why it is not recommended to use REPAIR with DBCC CHECKDB?

    Thanks,
    Abhijit

    Reply
  • 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!

    Reply
  • Hi
    Can you give me setp by step detail from windos start prompt to recover my database ?

    Reply
  • 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

    Reply
  • 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..

    Reply
  • Hello
    Can you give me setp by step detail from windos start prompt to recover my database ?
    please

    Reply
  • Atcheswara Reddy
    August 19, 2011 6:04 pm

    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

    Reply
  • Atcheswara Reddy
    August 19, 2011 6:06 pm

    What is internal process of check db..

    Reply
  • 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,

    Reply
  • 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.

    Reply
  • 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 0x00000000352000 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.

    Reply
  • what is the difference between DBCC CHECKDB in SQL SERVER 2005 and 2008

    Reply
  • how DBCC CACHESTATS command work?

    Reply
    • @Payal – That’s an undocumented command and may get removed in future version. Why don’t you use below?

      select * from sys.dm_exec_cached_plans

      Reply

Leave a Reply

Menu