25
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 2016 build (with DBCC errors reported) like this.
The second plan has a single row coming out of the merge anti semi join, the first plan zero rows.
The join ...
22
I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server.
First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, ...
17
The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.
The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when ...
13
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 ColumnStore Indexes
A more portable repro of the issue is this:
SELECT *, CONVERT(NVARCHAR(MAX), m.text + m.text + m.text + m.text + m.text) AS texty
INTO ...
12
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 contain database corruption if DBCC CHECKDB ever fails due to corruption. The FULL backup before the last successful DBCC CHECKDB is one without corruption.
To ...
12
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 deleted from compressed row groups.
As far as I can tell, everything is organized correctly in the internal system tables (on SQL Server 2017 CU3), and most of ...
11
DBCC uses snapshots internally. Snapshots are then implemented as sparse files in Windows.
So this is actually a problem with Windows' handling of sparse files, which causes the snapshots used by DBCC to occasionally break on large and very active databases. It's reasonably well documented with a few recommended fixes in http://support.microsoft.com/kb/...
10
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. This can
include quick repairs, such as repairing missing rows in non-clustered
indexes, and more time-consuming repairs, such as rebuilding an index.
As ...
10
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 checkdb operation does not touch in-memory tables, and the snapshot only captures changes to the on-disk tables.
Presumably, Microsoft chose to prevent user-created ...
9
Go here, and download/install the scripts.
If this is for production, I strongly advise you to not write the scripts yourself. It's far better (and easier!) to use an already-thoroughly-tested set of scripts instead of reinventing the wheel.
9
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 CHECKDB has on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full ...
9
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 you need to recover data from the backup.
The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn'...
9
First, thank you for the information and the reproducing code/situation.
I've taken this and filed an internal item, it's been assigned and will be looked at shortly.
You can vote for work at filegroup to read_only prevents dbcc checkdb from running on the SQL Server feedback site.
I'll update this answer with more information as it becomes available.
answered Feb 23 '18 at 12:18
Sean Gallardy - Retired User
20.6k2 gold badges32 silver badges63 bronze badges
9
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 only time this can happen is when an extent is a mixed extent
(where the 8 pages in the extent can be allocated to potentially 8
different allocations units – ...
9
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 dropped from 140 GB cached DB memory to 60 GB
This is correct, you can clearly see this behaviour when this example DBCC CHECKDB command completes at 21h45
Why
...
8
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 detailed information like this
and check a whole bunch of servers like this
It's really quick too. In my lab of 10 servers with 125 databases it completes in ...
8
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 that database (since it's read-only).
... and secondly if is safe as I have a primary server that is Live and I am doing a checkdb on a secondary during ...
answered Sep 13 '18 at 13:27
Sean Gallardy - Retired User
20.6k2 gold badges32 silver badges63 bronze badges
8
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 header page (page ID 0).
Page 9 (the database boot page).
Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and ...
8
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 there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason ...
7
Running CHECKDB against tempdb is not the same as running it against a user database.
From MSDN:
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, for performance reasons, database snapshots are not available on tempdb. This means that the ...
7
Personally, I wouldn't shrink the database. It just takes more time when it needs to grow again. It may also make file system fragmentation worse.
7
Further investigation shows that this is a bug in DBCC CHECKDB. A Microsoft Connect bug has been opened: Unfixable DBCC CHECKDB error (that is also a false positive and otherwise strange). Fortunately, I was able to produce a repro so that the bug can be found and fixed.
The bug can be hidden by playing with the database schema. Deleting an unrelated ...
7
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/day1of31daysofdisasterrecoverydoesdbccautomaticallyuseexistingsnapshot
Performance comparison of CHECKTABLE commands run against database vs manually created snapshot w/ ...
7
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 TABLE #Results
(
DatabaseName SYSNAME NULL
, IsOnline BIT NULL
, ParentObject varchar(100) NULL
, [Object] varchar(100) NULL
, [Field] varchar(...
7
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 DATABASE houseme SET MULTI_USER;
In case the name of the option isn't obvious enough, this can lead to data loss - but without backups, you don't really have ...
6
Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)
You can run DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS directly on the indexed views. Checking indexed views can be problematic in certain circumstances, so be prepared to investigate any ...
6
DBCC CHECKDB is vital for SQL Server databases to be 100% sure that there is no corruption. However, due to databases growing massive in size, its very hard to find a maintenance window when you claim to be 24x7 up. Over the years, SQL Server team has implemented various mechanisms that will detect most common forms of corruptions especially related to ...
6
Yes, but specifically, an catalog error can't be checked in TempDB. You should recycle SQL Server if possible to fix this. Per MSDN:
"Running DBCC CHECKCATALOG against tempdb does not perform any checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be ...
6
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 time.
Any DBCC CHECK* command has to scan all the data as the results from any previous DBCC CHECK* command are
not persisted anywhere
in no way can be trusted ...
6
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 the CHECKDB should continue as is.
See FIX: Error 976 occurs when you run the DBCC CHECKDB command on an unreadable secondary replica of SQL Server 2016 ...
Only top voted, non community-wiki answers of a minimum length are eligible
