DBCC CHECKDB checks database consistency. If it reports corruption, I preserve the output and establish the recovery options.
CHECKDB includes allocation, table, and catalog checks. The following commands explain those parts of the full database check.
- CHECKALLOC checks database allocation structures.
- CHECKTABLE checks table and indexed-view structures.
- CHECKCATALOG checks consistency within database metadata.
A full run also validates Service Broker data. At compatibility level 100 or higher, detailed logical checks of indexed views require EXTENDED_LOGICAL_CHECKS. PHYSICAL_ONLY and TABLOCK limit the checks performed.
-- Administrator check. Choose the database and a suitable resource window.
DBCC CHECKDB (N'YourDatabase') WITH NO_INFOMSGS;The command can consume I/O, CPU, and TempDB space. Run it under the appropriate operational plan or validate a restored copy when that is the chosen workflow. Retain the full output rather than only the final completion line.
Respond to an error with a recovery decision
Investigate the storage and environment as well as the database. A validated known-good backup is the preferred recovery starting point when available. Test the restored database and account for the recovery point before replacing a live database.
REPAIR_ALLOW_DATA_LOSS can remove data or structures. It is a last-resort repair option, not a substitute for recovery from a good backup. Don’t add it automatically to a consistency check.
CHECKALLOC, CHECKTABLE and CHECKCATALOG explain several checks within this process. I still keep backup and restore planning separate. A successful consistency check doesn’t prove that recovery files are available.
Reference: DBCC CHECKDB.
A repair command is not a recovery plan, it is a last resort with consequences for the data.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.
how to execute DBCC command on linked server.
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
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
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!
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
Pinal,
Thank you for your help..I appreciate it so much…
Anne
Pinal,
Why it is not recommended to use REPAIR with DBCC CHECKDB?
Thanks,
Abhijit
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!
Hi
Can you give me setp by step detail from windos start prompt to recover my database ?
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
Thnaks
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..
Hello
Can you give me setp by step detail from windos start prompt to recover my database ?
please
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
What is internal process of check db..
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,
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.
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.
what is the difference between DBCC CHECKDB in SQL SERVER 2005 and 2008
how DBCC CACHESTATS command work?
@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