26 votes
Accepted

Are unfixable spatial index corruptions considered normal?

I could not immediately reproduce this on 2014 - 12.0.4213.0 but do see it on SQL Server 2016 (CTP3.0) - 13.0.700.242. On the 2014 build (with no DBCC errors) the plan looks as follows. And on the ...
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 ...
  • 5,846
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 ...
  • 35.6k
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 ...
  • 76.9k
10 votes

How to repair database consistency errors

I would like to know if there is a way to fix this These consistency errors may be fixable with the REPAIR_REBUILD option of DBCC CHECKDB: Performs repairs that have no possibility of data loss. ...
  • 76.9k
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 ...
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 ...
  • 68.3k
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 ...
  • 35.6k
9 votes

Alternative of DBCC CHECKDB

The only alternative to performing consistency checking (DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss. There are some ways to reduce the impact that ...
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 ...
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 ...
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 ...
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 ...
  • 76.9k
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 ...
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 ...
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 ...
  • 18.7k
7 votes
Accepted

VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I think Robert Davis answered your question in two posts: How to manually create a database snapshot and get SQL Server to use it for CHECKDB: http://www.sqlsoldier.com/wp/sqlserver/...
7 votes

DBCC CHECKDB LastKnownGood

Instead of using sp_msForEachdb, which is problematic as Aaron described, you could use a cursor for this. IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL BEGIN DROP TABLE #Results; END CREATE ...
  • 68.3k
7 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 ...
  • 1,521
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 ...
  • 122
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 ...
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 ...
  • 18.7k
6 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 ...
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 ...
  • 1,842
5 votes

Getting error "Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7." But a database with Id 7 DOES NOT exist

run dbcc checkdb CHECKDB creates a database snapshot behind the scenes. This is your DBID 7. As for the timeout, perhaps KB919155 Error message when you run the DBCC CHECKDB statement on a database ...
  • 51.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 ...
  • 18.7k
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) ...
  • 61.6k
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 & ...
  • 61.6k
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 ...
  • 68.3k
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, ...
  • 61.6k

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