Skip to main content
13 votes
Accepted

Full backup before and after checkdb

There is no need to take a FULL backup before and after running DBCC CHECKDB. It is recommended that your FULL backup go before the DBCC CHECKDB so that you know exactly which FULL backups don't ...
Tara Kizer's user avatar
  • 5,966
13 votes
Accepted

Physical only checkdb is failing, but full one is completed successfully

Yes, this a defect in SQL Server 2017 up to CU14. There was a similar issue with nonclustered columnstore indexes in SQL Server 2016. It's detailed in this video: CHECKDB Bug With SQL Server 2017 And ...
Erik Darling's user avatar
  • 41.9k
12 votes
Accepted

Columnstore Index in read_only filegroup prevents CheckDB

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table. Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically ...
Paul White's user avatar
  • 86.4k
10 votes

DBCC CHECKDB consistency-based I/O error

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and ...
Aaron Bertrand's user avatar
10 votes
Accepted

Why is CHECKDB reading the transaction log file on a database with a memory optimized table?

Even though the SQL Server documentation states that databases with "In-Memory" tables do not support snapshots, the "internal" snapshot required for DBCC CHECKDB can still be created since the ...
Hannah Vernon's user avatar
  • 70.2k
10 votes

DBCC checkdb on tempdb

No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if ...
Erik Darling's user avatar
  • 41.9k
9 votes
Accepted

How to fix errors reported by DBCC CHECKDB

The output of DBCC CHECKDB has told you exactly what to do: USE [master]; GO ALTER DATABASE houseme SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB(N'houseme', REPAIR_ALLOW_DATA_LOSS); ALTER ...
Aaron Bertrand's user avatar
9 votes
Accepted

Backup detects corruption, but CHECKDB does not

This answer is taken from an issue of the SQLskills.com newsletter written by Paul Randal, about "a database which would fail a backup with page checksum errors, but passed a DBCC CHECKDB". The ...
Randolph West's user avatar
9 votes
Accepted

Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric Database Cache Memory (KB) drops down significantly. If to be exact, it ...
Randi Vertongen's user avatar
9 votes

SQL Server sample database WideWorldImporters fails dbcc checkdb

The error message is a little misleading. It's not that the statistics object associated with the primary key index is corrupt; rather the DBCC check for statistics corruption encounters an assertion ...
Paul White's user avatar
  • 86.4k
8 votes

DBCC CHECKDB LastKnownGood

I would use the PowerShell Command from the dbatools module Get-DbaLastGoodCheckDb which will enable you to check all of your servers in one go. Here is a sample output from the command You can get ...
SQLDBAWithABeard's user avatar
8 votes
Accepted

Allocate space for object 'dbo.SORT temporary run storage

This is pretty self explanatory - your tempdb is full, and can't be grown to perform the operation it needs tempdb for. As indicated, either the disk where it is located is full, or you haven't got ...
Ian_H's user avatar
  • 1,656
8 votes

DBCC CheckDb on Secondary Asynchronous Replica

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours? Sure, assuming the routine doesn't try to update any command or history tables in ...
Sean Gallardy's user avatar
8 votes

DBCC CHECKDB on Always On databases

Yes you really need to run checkdb on ALL the secondary replicas. If you read MS document on Automatic Page repair it says Automatic page repair cannot repair the following control page types: File ...
Shanky's user avatar
  • 19k
7 votes

Allocate space for object 'dbo.SORT temporary run storage

Run this command to get the estimated tempdb space required for the check: DBCC CHECKDB ('databasename') WITH ESTIMATEONLY See the DBCC CHECKDB documentation ESTIMATEONLY Displays the estimated ...
Sathishchandra BhanuMurthy's user avatar
7 votes
Accepted

DBCC CheckDb on Secondary Asynchronous Replica

This method is not safe because it is almost meaningless. It is a quote of P. Randal who is author/architect of DBCC CHECKDB and SQL Server storage subsystem. Any of the answers where DBCC CHECKDB ...
Alex S's user avatar
  • 122
7 votes

Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?

SQL Server expert Paul Randal discusses this option in his post CHECKDB From Every Angle: Consistency Checking Options for a VLDB Use a separate system This alternative is relatively simple – restore ...
Scott Hodgin - Retired's user avatar
6 votes

SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica

I can say that the error message you are getting is because of a bug in SQL Server 2016. Microsoft knows about it and has fixed it in CU3 for SQL Server 2016 RTM. Note: The user error is reported but ...
Shanky's user avatar
  • 19k
6 votes

Will a DBCC CHECKDB with repair have to rescan?

To add to the answer above, if you're on Enterprise Edition, where a normal DBCC CHECKDB will run in parallel, when you run with a repair option it must run single-thread, so it will also take more ...
Paul S. Randal's user avatar
6 votes

SQL Server sample database WideWorldImporters fails dbcc checkdb

I can reproduce your issue on 2019 CU15 Developer Edition. I initially had 2019 RTM installed and that dbcc came back clean. Doing some investigation, it looks like these stats are associated with ...
Ben Thul's user avatar
  • 1,947
5 votes

voluntarily produce an error with DBCC CheckDb

Would anyone know how I can make DBCC CheckDB produce an error? Use the lethal dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool]) ON a TEST (NON-PROD) ...
Kin Shah's user avatar
  • 62.3k
5 votes
Accepted

voluntarily produce an error with DBCC CheckDb

Handy corruption demo script has been created by Paul Randal. These are really good and would help you a lot. Please browse below links Corruption demo databases and scripts Using DAC connection to ...
Shanky's user avatar
  • 19k
5 votes
Accepted

Will a DBCC CHECKDB with repair have to rescan?

will it need to take the same amount of time to scan for errors Yes it has to do it again .. scan and fix the errors with potential data loss. Be cautious, use it as a last resort - repair & ...
Kin Shah's user avatar
  • 62.3k
5 votes

Unexplainable DBCC CHECKDB Command Output

The error message you're seeing: Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C. Occurs when sqlcmd is asked to quit while it is waiting for a response from SQL ...
Hannah Vernon's user avatar
  • 70.2k
5 votes

DBCC checkdb on tempdb

Complementing to Erik's answer Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, ...
Kin Shah's user avatar
  • 62.3k
5 votes
Accepted

Is there any specific differences on table level integrity check between DBCC CHECKDB and DBCC CHECKTABLE?

DBCC CHECKDB: Checks the logical and physical integrity of all the objects in the specified database by performing the following operations: Runs DBCC CHECKALLOC on the database. Runs DBCC ...
Paul White's user avatar
  • 86.4k
5 votes
Accepted

How to get `dbcc checkdb` report when run via agent job?

You want the output from the DBCC CHECKDB command how? In the job history (sysjobhistory in msdb) as you can open in SSMS? As a text file? Included in an email sent to you? For 1, this is already ...
Tibor Karaszi's user avatar
4 votes
Accepted

DBCC CHECKDB returning space issue error

The message is telling you why it failed, here is the root cause of the failure: Write to sparse file 't:\test_audit23.ndf_MSSQL_DBCC6' failed due to lack of disk space. Looks like the T volume ...
Sean Gallardy's user avatar
4 votes
Accepted

BACKUP WITH CHECKSUM works but DBCC CHECKDB detects problems

sp_BlitzErik wrote in a comment (since deleted): ...is asking [about the PAGE_VERIFY_OPTION] because of this: Blitz Results: Page Verification Not Optimal. The CHECKSUM option for backups will only ...
Paul S. Randal's user avatar
4 votes

sp_MSforeachdb Continue on failure when running DBCC CHECKDB

I know this is an old post, but I'm adding this answer in case someone comes across it. The most likely cause is that the error is so severe (it has a severity of 20 or higher, most likely 22, 23 or ...
HandyD's user avatar
  • 9,982

Only top scored, non community-wiki answers of a minimum length are eligible