Questions tagged [dbcc-checkdb]
Checks the logical and physical integrity of all the objects in the specified database. Should be used to identify pages marked as suspect so they can be restored from backup or otherwise fixed.
179 questions
0
votes
1
answer
112
views
Deleting table with metadata corruption on SQL Server
We are trying to recover from an event and struggling with some issues that have been created within our database. One of our tables (Users) seems to have gotten corrupted somehow, as we get this ...
0
votes
1
answer
53
views
What is the minimum level of DBCC CHECKDB needed to check full-text indexes?
I have a database containing an unreasonably large full-text index. DBCC CHECKDB WITH PHYSICAL_ONLY takes about 25 minutes on this database, but DBCC CHECKDB WITH DATA_PURITY, EXTENDED_LOGICAL_CHECKS ...
- 823
0
votes
1
answer
43
views
Is DBCC CHECKFILEGROUP worthless if you already run DBCC CHECKDB on all databases?
DBCC CHECKDB makes many other integrity checks redundant, but I can't see anything in the documentation saying if it makes DBCC CHECKFILEGROUP redundant. Is DBCC CHECKFILEGROUP worthless if you ...
- 823
4
votes
1
answer
342
views
Backups/CHECKDB errors - "Cannot find server certificate"
I have been exchanging TDE certificates on various servers to make restores easier - some had the wrong name, the wrong thumbprint, or even both. This involves transferring databases to a temporary ...
0
votes
0
answers
69
views
Backup database in SQL Server 2012 and restore this backup in SQL Server 2014
First what I do is to check consistency of database in SQL Server 2012:
CHECKDB (DATABASE1)
And it is ok, no errors and warnings.
Next I do FULL backup and restore it on another machine with has SQL ...
- 101
1
vote
2
answers
88
views
My backup failed DBCC CHECKDB. Now what?
Assume the following:
I have a database on a production server. I do not regularly run DBCC CHECKDB on it.
I regularly take full, dif, and transaction log backups on my database.
As is good practice, ...
- 823
1
vote
1
answer
189
views
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS fails when run on Secondary replica
Create the simplest database possible:
CREATE DATABASE DatabaseCheck
Add this database to an Availability Group, where secondary is not readable
On primary run:
DBCC CHECKDB ('DatabaseCheck') WITH ...
- 595
0
votes
1
answer
182
views
Whether to run integrity check before or after backups (Ola H FAQ)?
Going through Ola H FAQ's, https://ola.hallengren.com/frequently-asked-questions.html, I see that
I also recommend that you perform the full backup after the integrity
check.
Isn't the opposite true?...
- 3,499
0
votes
1
answer
650
views
No catalog entry found for partition ID in database 7
I would like some help to fix the database problem.
I went to restore a database and I couldn't, so I executed DBCC CHECKDB (N'DATABASE_NAME') and the following result is displayed:
CHECKDB found 0 ...
-2
votes
1
answer
487
views
Database Integrity check fails as follows
(I have used the Ola Hallengren scripts for quite a while)....but if there is an open query I end up with the following:
Msg 5030, Sev 16, State 12, Line 1 : The database could not be exclusively ...
- 1
0
votes
1
answer
73
views
What is the table design for DBCC History of the command DBCC CheckDB on SQL Server 2019
I cannot find anything other than a SQL Server 2017 table structure which lists [RepairLevel] as an int, which in SQL Server 2019 it isn't, it's a string of sorts, if I set it to varchar(7000), the ...
- 163
1
vote
2
answers
1k
views
Foreign key constraint not showing in sysobjects but cannot be created due to conflict with itself
I have an Microsoft SQL Server 2019 Enterprise Edition database where I had a relationship between two tables: Properties and Photos with Properties being the primary key and Photos being the foreign ...
- 143
4
votes
1
answer
276
views
Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS?
Reluctant DBA here.
Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS? I am well aware MS recommends doing a backup.
What's the worse that could happen if I run a DBCC CHECKTABLE using ...
- 143
1
vote
0
answers
122
views
There are Suspect Pages but DBCC CHECKDB returned no errors?
As the question states, there are 3 suspect pages on a database, but when I ran DBCC CHECKDB (DB, REPIAR_REBUILD), after almost 4 hours, it completed with no errors. I know suspect pages might not ...
- 29
0
votes
0
answers
141
views
Corruption in backup but valid checksum
We have corruption in one of our full backups but I can't find an explanation how it happened. The server is a SQL Server 2019 Enterprise edition (CU17) with 5 databases in an AG. The backups are ...
- 2,114
1
vote
0
answers
82
views
What is the way to handle corruption in db having simple recovery model?
A database in simple recovery model cannot have log backups.
In this situation what is the way to handle corruptions reported by dbcc checkdb without data loss?
The documentation says:
Use the REPAIR ...
- 3,499
3
votes
1
answer
143
views
Formula for memory grant for DBCC CHECKDB
How is the memory grant for a DBCC checkdb determined (other than the max size for memory grants which is from resource governor, possibly the default pool)?
I'm not trying to solve a specific problem....
- 817
0
votes
2
answers
338
views
Take Backup of a Database without the Corrupted rows
I have DB of size 170GB. One of the table has 339500 rows out of which around 2000 rows are corrupted.
When I tried backing up the database I get "Backup with 'Checksum' gave error"
SQL ...
- 105
-3
votes
1
answer
386
views
How to get `dbcc checkdb` report when run via agent job?
When I run dbcc checkdb via management studio, then I get the full report as output.
However when I run dbcc checkdb via sql agent job, then I think that the job status will either show as success if ...
- 3,499
1
vote
2
answers
435
views
DBCC CHECKDB Error 2706
A CHECKDB job has recently started returning error number 2706 for 2 databases (msdb and a user database).
The msdb.dbo.suspect_pages table is empty though. When I run CHECKDB manually it says:
...
- 65
0
votes
1
answer
92
views
What is the difference between dbcc checks and suspect pages table in terms of data page corruption?
Dbcc checkdb returns errors if it detects corruption on data pages.
Similarly the suspect pages table hold corruption info of data pages.
What is the difference between dbcc checks and suspect pages ...
- 3,499
0
votes
1
answer
1k
views
Crash of MSSQL CHECKDB with EXCEPTION_ACCESS_VIOLATION
I have a db with some consistency errors. Below the messages I get when issuing a CHECKDB on it with repair_rebuild option.
CHECKDB found 0 allocation errors and 16 consistency errors in database '...
clebig
9
votes
2
answers
540
views
SQL Server sample database WideWorldImporters fails dbcc checkdb
Problem Statement
I just ran dbcc checkdb on the sample WideWorldImporters database. I received (4) errors about statistics corruption that I do not remember having seen in the past when I ran this ...
6
votes
1
answer
4k
views
DBCC CHECKDB on a user database: Time-out occurred while waiting for buffer latch type 2 for page (X:XXX), database ID 2
Our Ola Hallengren IntegrityCheck job failed due to a buffer latch timeout while running DBCC CHECKDB on a user database.
However, the buffer latch timeout reported was in TempDB (database ID 2).
...
- 483
2
votes
1
answer
571
views
How to check which pages are part of an extent?
I have some data corruption and was not able to restore the data via backups. It was affecting mainly non-clustered indexes, so after drop-create of the indexes the issues were resolved.
Finally, I ...
- 4,318
0
votes
1
answer
754
views
DBCC CHECKDB job failed on SQL Server 2014
We have a DBCC CHECKDB job that runs weekly on Saturdays. This job was created thorugh Maintenance Plan wizard. Typical duration for this job is 1.5 hours. This past weekend the job failed with the ...
- 83
0
votes
1
answer
1k
views
Speeding up DBCC CHECKDB on a large database
I have 16 GB RAM, 12 GB allocated for SQL Server, total size of all databases on an instance 5TB.
DBCC CheckDB with PhysicalOnly checking on the biggest database ~ 3TB.
1 socket, 2 virtual processors.
...
- 707
0
votes
1
answer
553
views
How to query database integrity check duration time (how much time did it take to complete dbcc checkdb) [duplicate]
How to know how much time did it take to run database integrity check for all databases on a instance.
I simply need when it started and when its finished.
I am able to query when the last check db ...
- 707
3
votes
0
answers
124
views
SQL Server should I format with ReFS to get rid of operating system error 665? [duplicate]
I have 13 production SQL Servers
Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
- 2,039
0
votes
1
answer
716
views
Strategies when DBCC CheckDB REPAIR_ALLOW_DATA_LOSS can't repair a database
To get it out of the way, in this scenario we are repairing a number database where no backups were taken from before the corruption, so restoring a backup is not an option. Not my database :)
Upon ...
- 133
3
votes
2
answers
2k
views
DBCC consistency error in SQL Server
I ran DBCC CheckDB in our Production SQL Server 2014. It reported back with one consistency error:
Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'MYDB') WITH NO_INF..." failed ...
- 83
1
vote
2
answers
409
views
DBCC CHECKDB not sending an alert when Corruption Found for Severity Level 16
I have setup this alert that I got from Brent Ozar's Website.
EXEC msdb.dbo.sp_add_alert
@name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@...
- 11
0
votes
1
answer
593
views
Is there any specific differences on table level integrity check between DBCC CHECKDB and DBCC CHECKTABLE?
I've faced with allocation error in one of our databases while running DBCC CHECKDB on it. It throws below-mentioned error:
Msg 8947, Level 16, State 1, Line 5
Table error: Multiple IAM pages for ...
- 240
1
vote
1
answer
658
views
DBCC Check DB fails due to operating system error 665
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically ...
- 1,063
0
votes
1
answer
642
views
dbcc checkdb differences between physical_only, full and data_purity
I'm getting lost about one thing. According to MS docs full checkdb with no option does column-value checks, so what's the gain of running data_purity? By 'gain' I mean how detailed checks are being ...
- 23
-1
votes
1
answer
799
views
How to checkdb automatically in AWS RDS?
I'm pretty new to Amazon AWS and RDS. I have migrated my SQL Database to RDS and I have it up and running. I'm now looking to automate maintenance tasks. Anyone with experience with RDS can help me ...
- 103
0
votes
1
answer
215
views
Overhead associated with DBCC CHECKTABLE?
Have a logshipped VLDB and don't run DBCC CHECKDB on primary due to the long running periods of high CPU and tempdb usage. Have read about DBCC CHECKTABLE as a possibility for a more granular data ...
- 1,127
0
votes
2
answers
322
views
Does page verification mode affect DBCC CHECKDB behavior?
Does DB page verification mode (torn page vs checksum) affect DBCC CHECKDB behavior? Have some large (TBs) older db's still with TORN page detection mode and curious if check db works any better / ...
- 1,127
2
votes
2
answers
3k
views
DBCC CheckDB space requirements?
Ran into the following error...
K:\Microsoft SQL
Server\Data\MyAudit3_Dat.NDF_MSSQL_DBCC50: Operating system error
112(There is not enough space on the disk.) encountered.
In this case the disk ...
- 1,127
4
votes
2
answers
807
views
Multiple volumes vs single volumes for SQL Server CHECKDB performance
We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple ...
- 43.2k
1
vote
1
answer
490
views
SQL Server Database size - doubled running DBCC
I was running a dbcc command using ola hallengren checkdb on a large database (775Gb) when half way through the process the tempdb ran out of space. I have run this in other environment on different ...
- 11
0
votes
1
answer
105
views
SQL Server online maintenance - False publicity?
I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance. errors like timeouts, scan stopped due to data movement ...
- 1,093
2
votes
1
answer
220
views
CHECKDB with default settings and parallelism involved [closed]
This would be my follow-up question because I am trying to understand the CHECKDB behaviors
While running CHECKDB without any MAXDOP specified on below 2 servers:-
Server with 10 Vcores, MAXDOP at ...
- 794
0
votes
1
answer
641
views
DBCC CHECKDB behavior with MAXDOP settings
I am trying to understand the DBCC CHECKDB usage with MAXDOP settings-
If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my ...
- 794
-3
votes
1
answer
1k
views
SQL Server Integrity Check error
I’ve Implemented Ola's SQL Server Integrity Check on one of my SQL 2012 Servers and created a SQL Agent job to run it.
The job fails every time I run it, but when I run the code below manually it ...
- 11
1
vote
0
answers
482
views
Database in suspect mode
I'm experiencing a tough issue for me. When I was updating some columns into the database I have previously installed, the main memory corruption led the database jump into the suspect mode. I follow ...
- 11
2
votes
1
answer
219
views
DBCC CHECKTABLE - 2570 error
Running DBCC Checktable on one of my tables causes several errors:
Msg 2570, Level 16, State 3, Line 1 errors, Page (1:442), slot 1 in
object ID 565577053, index ID 0, partition ID ...
- 313
2
votes
2
answers
2k
views
dbcc checkdb runs for interminably long time, 50% of the time
I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database.
The integrity check part is ...
- 21
0
votes
1
answer
293
views
CHECKDB problem - SQL files (mdfs,ldfs etc.) in Azure Files
Anybody out there using Azure Files for SQL data and log file storage? I've got one SQL Server using an AF share in this manner and am having issues running CHECKDB on its databases. Has anyone out ...
- 81
0
votes
1
answer
251
views
DBCC CHECKDB running longer
We are using Ola's Maintenance solution and part of that we have job DatabaseIntegrityCheck - User Database and System Databases, sometimes job for some of the databases it's running longer like in ...
- 11