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.
172
questions
0
votes
1
answer
45
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,442
0
votes
1
answer
191
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
144
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
50
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 ...
- 143
1
vote
2
answers
528
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
259
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
68
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
76
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 ...
- 1,994
1
vote
0
answers
42
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,442
3
votes
1
answer
100
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....
- 795
0
votes
2
answers
204
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 ...
-3
votes
1
answer
286
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,442
1
vote
2
answers
329
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:
...
- 55
0
votes
1
answer
79
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,442
0
votes
1
answer
835
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
480
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
3k
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).
...
- 463
2
votes
1
answer
412
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 ...
- 3,780
0
votes
1
answer
635
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
720
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.
...
- 657
0
votes
1
answer
378
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 ...
- 657
3
votes
0
answers
112
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 ...
- 1,999
0
votes
1
answer
534
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
1k
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
301
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
473
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 ...
- 230
1
vote
1
answer
408
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,039
0
votes
1
answer
448
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
597
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
196
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,087
0
votes
2
answers
205
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,087
1
vote
2
answers
2k
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,087
4
votes
2
answers
744
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 ...
- 42.7k
1
vote
1
answer
354
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
88
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,071
2
votes
1
answer
167
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 ...
- 774
0
votes
1
answer
437
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 ...
- 774
-3
votes
1
answer
857
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
401
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
177
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 ...
- 303
2
votes
2
answers
1k
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
258
views
CHECKDB problem - SQL files (mdfs,ldfs etc.) in Azure Files
Anybody out there using Azure Files (https://learn.microsoft.com/en-us/azure/storage/files/storage-files-introduction) for SQL data and log file storage? I've got one SQL Server using an AF share in ...
- 41
0
votes
1
answer
187
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
0
votes
1
answer
120
views
Index / Check DB Script question
I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
0
votes
0
answers
202
views
How does DBCC CHECKDB use tempdb?
I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly ...
- 1,253
0
votes
1
answer
58
views
Running CheckDB on a clone
My company are looking to bring in Redgate's SQL Clone to deploy live databases into other environments. I wanted to see if running CheckDB on these clones was a viable option instead of restoring a ...
- 13
1
vote
1
answer
243
views
Integrity checks permissions issue
I am running Hallengren's IntegrityChecks job which fails with the following error:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the ...
- 369
4
votes
2
answers
1k
views
PFS Page corruption issue and question
We recently encountered PFS page corruption in our production DB. We are in the process of checking old backups to find which ones are clean, but I was wondering if a different process would work to ...
- 257
1
vote
2
answers
953
views
how to find out how long the last DBCC checkdb took to complete?
In most of my servers I have dbcc checkdb scheduled to run.
I can get the last known good DBCC Checkdb through DBCC DBINFO().
I can read the tracks left on the sql server log and I get something ...
- 15.8k
8
votes
1
answer
511
views
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 dropped ...
- 5,482