Skip to main content

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
67 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 ...
piotrassss's user avatar
1 vote
2 answers
69 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, ...
J. Mini's user avatar
  • 509
0 votes
0 answers
84 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 ...
rois's user avatar
  • 558
0 votes
1 answer
115 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?...
variable's user avatar
  • 3,566
0 votes
1 answer
445 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 ...
Chris Samaritano's user avatar
-2 votes
1 answer
315 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 ...
Ian Cope's user avatar
0 votes
1 answer
63 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 ...
BLoB's user avatar
  • 163
1 vote
2 answers
863 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 ...
Daniel's user avatar
  • 143
4 votes
1 answer
271 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 ...
Edgar's user avatar
  • 143
1 vote
0 answers
98 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 ...
DBANoob's user avatar
  • 29
0 votes
0 answers
116 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 ...
Frederik Vanderhaegen's user avatar
1 vote
0 answers
76 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 ...
variable's user avatar
  • 3,566
3 votes
1 answer
118 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....
xhr489's user avatar
  • 807
0 votes
2 answers
281 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 ...
IT Researcher143's user avatar
-3 votes
1 answer
336 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 ...
variable's user avatar
  • 3,566
1 vote
2 answers
390 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: ...
td48260's user avatar
  • 55
0 votes
1 answer
84 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 ...
variable's user avatar
  • 3,566
0 votes
1 answer
927 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 '...
user avatar
9 votes
2 answers
503 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 ...
Tom Wickerath's user avatar
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). ...
Eitan Blumin's user avatar
2 votes
1 answer
499 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 ...
gotqn's user avatar
  • 4,026
0 votes
1 answer
728 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 ...
sqllover2020's user avatar
0 votes
1 answer
929 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. ...
Serdia's user avatar
  • 697
0 votes
1 answer
479 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 ...
Serdia's user avatar
  • 697
3 votes
0 answers
121 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 ...
Danielle Paquette-Harvey's user avatar
0 votes
1 answer
632 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 ...
Thomas's user avatar
  • 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 ...
sqllover2020's user avatar
1 vote
2 answers
359 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, @...
BRJ5144's user avatar
  • 11
0 votes
1 answer
531 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 ...
Elvin Ahmadov's user avatar
1 vote
1 answer
558 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 ...
SQL_NoExpert's user avatar
  • 1,061
0 votes
1 answer
563 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 ...
MadSlavDBA's user avatar
-1 votes
1 answer
712 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 ...
Carlos Sosa's user avatar
0 votes
1 answer
209 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 ...
user2368632's user avatar
  • 1,097
0 votes
2 answers
264 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 / ...
user2368632's user avatar
  • 1,097
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 ...
user2368632's user avatar
  • 1,097
4 votes
2 answers
775 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 ...
Brent Ozar's user avatar
1 vote
1 answer
424 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 ...
tbear58203's user avatar
0 votes
1 answer
94 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 ...
JohnG's user avatar
  • 1,081
2 votes
1 answer
195 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 ...
Newbie-DBA's user avatar
0 votes
1 answer
546 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 ...
Newbie-DBA's user avatar
-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 ...
Peter's user avatar
  • 11
1 vote
0 answers
433 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 ...
ccm's user avatar
  • 11
2 votes
1 answer
205 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 ...
Andrzej Błoch's user avatar
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 ...
rheinz's user avatar
  • 21
0 votes
1 answer
279 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 ...
BBaggins's user avatar
0 votes
1 answer
227 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 ...
pds's user avatar
  • 11
0 votes
1 answer
127 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.
Anil Gupta's user avatar
0 votes
0 answers
251 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 ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
66 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 ...
Krishn's user avatar
  • 13
1 vote
1 answer
259 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 ...
jack's user avatar
  • 389