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.
184 questions
4
votes
1
answer
131
views
How can I tell which tables, indexes, or pages do not have checksums?
This time last year, I set the page_verify database option to checksum on a very old and very large database. We do weekly index maintenance, so I hope that most of the database's pages have been ...
- 1,322
2
votes
1
answer
118
views
What are the downsides of running a Physical Only Database Integrity Check job
I am unable to find an article yet, that is able to clearly help my understanding of the risks I run by running my Ola Hallengren DatabaseIntegrityCheck job with PHYSICALONLY parameter on our large ...
- 447
3
votes
1
answer
150
views
CHECKDB works on an Enterprise Edition readable secondary, but not CHECKCATALOG
I have an Enterprise Edition SQL Server 2022 cluster with multiple AGs.
Two of the AGs consider different instances primary. Both of these consider their secondaries readable and are correct to think ...
- 1,322
3
votes
2
answers
613
views
Why is running database integrity checks in parallel beneficial if you are following best practice on Enterprise Edition?
On Enterprise Edition and Development edition, database integrity checks (e.g. DBCC CHECKDB) can run in parallel. According to best practice, backups should be tested regularly. So if I'm following ...
- 1,322
0
votes
1
answer
90
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 ...
- 1,322
4
votes
1
answer
729
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 ...
1
vote
2
answers
133
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, ...
- 1,322
1
vote
1
answer
285
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,602
-2
votes
1
answer
821
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
1
vote
2
answers
2k
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
1
vote
0
answers
150
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
1
vote
0
answers
99
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,602
0
votes
2
answers
389
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
1
vote
2
answers
496
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
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
6
votes
1
answer
5k
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).
...
- 482
0
votes
1
answer
798
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 ...
- 73
0
votes
1
answer
758
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 ...
- 717
0
votes
1
answer
850
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 ...
- 143
1
vote
2
answers
477
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
2
votes
1
answer
804
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,117
-1
votes
1
answer
900
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
2
answers
402
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,141
4
votes
2
answers
856
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.4k
0
votes
1
answer
130
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