Share via

DBCC consistency error in SQL Server

SQLLover21 201 Reputation points
2021-08-17T15:31:21.587+00:00

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, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'MYTABLE' (object ID 629577281).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'MYDB'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MYDB).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Since we do not have a good backup for that DB, we have to route to the below scenario.

What I did to troubleshoot:

I restored the recent backup file from Prod to UAT and ran DBCC CheckDB again to replicate the error. The same consistency error came back.
Ran DBCC CHECKTABLE (MYTABLE) = same consistency error shows: 

Table error: Object ID 629577392, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). 

The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.
DBCC results for 'MYTABLE'.
There are 53635 rows in 2705 pages for object "MYTABLE".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'MYTABLE' (object ID 629577281).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE

Then implemented the following steps below:
-- step 1 ALTER DATABASE [MYDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- step 2 DBCC CHECKDB(N'[MYDB]', REPAIR_ALLOW_DATA_LOSS); 

     Msg 8964, Level 16, State 1, Line 3
        Table error: Object ID 629577392, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.
                The error has been repaired.
        There are 53635 rows in 2705 pages for object "MYTABLE".
  • step 3 DBCC CHECKDB ('[MYDB]') ---(Re-run to check for any additional errors = no errors reported)

-- step 4 ALTER DATABASE [MYDB] SET MULTI_USER; ---(setting it back to multi user mode = users can access the DB)

My questions/concerns:

How do I know which data has been lost? From my understanding it doesn't seem like any data was lost because before the repair the table had 53635 rows. After the repair it still has 53635 rows.

Do we need to to schedule downtime/ outage for that exact site when setting the DB to single user mode?

After doing the repair in Production, what are some best practices to keep aware of?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.1K Reputation points
    2021-08-17T21:16:17.14+00:00

    You can run

    ``´
    SELECT keycol, datalength(lobcol) FROM MYTABLE 

    in an attempt to find which LOB that was affected. However, note that queries on a corrupted table may not return correct results. The error message sounds to me that DBCC found something which is without reference, so the data is kind of lost already before DBCC.

You could run DBCC PAGE on the page in the error message to see what is there, but it is not exactly a trivial exercise.

I think you should be happy that it was just a single LOB value that was lost. Be sure to have good backups in the future!

PS! Good work with restoring to UAT and running REPAIR_ALLOW_DATA_LOSS there!
  2. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-08-18T07:15:53.043+00:00

    Hi SQLLover21-0870,

    We can try to use tablediff.exe in cmd to compare the old database backup with the repaired new database to see what changes have been made. The tablediff utility is located at \Program Files\Microsoft SQL Server\120\COM.
    Please refer to this doc for more details.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments