Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dbcc-checktable]

Use the dbcc-checktable tag for questions about how to implement, understand, and diagnose issues with the SQL Server DBCC CHECKTABLE command

DBCC CHECKTABLE checks the integrity of all the pages and structures that make up a table or an indexed view in Microsoft SQL Server.

For the specified table, DBCC CHECKTABLE checks for the following:

  • Index, in-row, LOB, and row-overflow data pages are correctly linked.

  • Indexes are in their correct sort order.

  • Pointers are consistent.

  • The data on each page is reasonable, included computed columns.

  • Page offsets are reasonable.

  • Every row in the base table has a matching row in each nonclustered index, and vice-versa.

  • Every row in a partitioned table or index is in the correct partition.

  • Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

To check every table in a database, use the DBCC CHECKDB command.

10 questions
7
votes
1 answer

DBCC CHECKTABLE taking 15+ minutes to run on an empty table

I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run. When it finishes there are no failures or errors. The performance on everything else on the server is at a very acceptable form. There was…
Russ960
  • 361
  • 4
  • 14
6
votes
1 answer

Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

We have an OrderLines table with columns: Quantity int not null QtyCancelled int not null QtyBackorder int not null QtyPicking int not null QtyPacking int not null QtyShiped int not null with computed and persisted column FulfillmentStatusId…
JW-OP
  • 141
  • 5
4
votes
1 answer

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 REPAIR_ALLOW_DATA_LOSS? A bit of background. A client…
Edgar
  • 143
  • 3
3
votes
2 answers

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 with the following error: "Table error: Object ID 629577392,…
sqllover2020
  • 73
  • 2
  • 13
2
votes
0 answers

DBCC TABLE CHECK command won't stop

We have a SQL Server 2014 with 140 user databases, each of which have approx. 180 tables. A maintenance plan was scheduled for once a week but it was discovered that the Check Database Integrity (DBCC CheckDB) job was taking too long. The schedule…
jock
  • 21
  • 2
2
votes
1 answer

VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have space to restore a copy on another server connected…
Todd Kleinhans
  • 23
  • 2
1
vote
1 answer

Why does DBCC CHECKTABLE WITH TABLERESULTS only report Error 8990 on corrupt tables?

I have a production VLDB that I'm using Paul Randal's guidance on to spread DBCC CHECKDB workload over a week. I'm logging the results of DBCC CHECKTABLE WITH TABLERESULTS and DBCC CHECKALLOC WITH TABLERESULTS to a table an noticed DBCC CHECKTABLE…
S3S
  • 3,578
  • 1
  • 14
  • 25
0
votes
1 answer

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 object ID 1277199566, index ID 1, partition ID…
Elvin Ahmadov
  • 240
  • 1
  • 8
0
votes
2 answers

What causes DBCC CHECKTABLE to return "session is in the kill state"?

A scheduled job step ran DBCC CHECKTABLE on a table but returned the following error: Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed. What causes this error?
user2368632
  • 1,133
  • 1
  • 15
  • 33
0
votes
1 answer

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 integrity checking option. Does DBCC CHECKTABLE…
user2368632
  • 1,133
  • 1
  • 15
  • 33