3

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:

  1. 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.
  2. 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
  1. 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:

  1. 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.

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

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

1
  • Looks like it's data from an off-row LOB column (such as varchar(max) or varbinary(max)) and DBCC has deleted it. Given that it wasn't referenced by any row, either the row lost the reference somehow (you'll have to work out which row is missing data), or it was supposed to be null anyway. Aug 18, 2021 at 2:14

2 Answers 2

0

How do I know which data has been lost?

Well that is really difficult to find AFAIK, the best way is test your database and app functionality. You said the data rows are same please also check the constraints and business rules. Since the corruption was limited to one table and you were able to take backup and do a restore, I guess corruption was not that severe. In this case you can run checkdb repair on newly restored database and then compare the table from both the database. Well that is tough but there is hardly ant other way.

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

Yes, as name suggests if single user is allowed others needs to be kicked off including application. Repair of a database is offline operation so downtime will be needed.

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

Run checkdb again on the database and if it does not points to any error than I guess your repair was successful. Please also note there are something which Checkdb cannot fix. If you want to make this short because database is big then you can run dbcc checkconstraint(). Complete checkdb will include checkconstraint.

Also Read How Does EMERGENCY-Mode Repair Work?

2
  • Before the repair, There are 53635 rows in 2705 pages for object "AllDocs". After the repair: The error has been repaired. There are 53635 rows in 2705 pages for object "AllDocs". --- this is the exact same. Also I queried the row counts in PRD (bad DB) and row counts in UAT(good DB), and they were the same row counts. How much downtime might be required? When I tested it out in UAT, it took roughly 10 mins. Aug 17, 2021 at 18:19
  • Downtime depends on size of your database and extent of corruption. Repair will take its own sweet time. That is why restoring from known valid backup is more preferred option.
    – Shanky
    Aug 18, 2021 at 9:03
0

It looks like it's orphaned data from an off-row LOB column (such as varchar(max) or varbinary(max)) and DBCC has deleted it. No whole rows were deleted.

Given that it wasn't referenced by any row, either the row lost the reference somehow and you'll have to work out which row is missing data; or it was supposed to be set to null anyway and the data ended up orphaned.


As documented, to use a repair option, the database must be in single-user mode. Therefore downtime must be scheduled.

However, you can repair just that one table using DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. This will minimize the downtime.


Databases are normally very resilient. Even an unexpected shutdown is normally recoverable to a consistent state.

Generally, database inconsistency can come from one of only a few things:

  • Physical disk fault. For this, the best solution is mirrored drives.
  • Operating system issues, such as buggy drivers or antivirus, an actual virus/malware, other OS bug. Of these, buggy drivers are the most likely.
  • Memory fault. This is highly unlikely with ECC server RAM.
  • On drives without Power Loss Protection, an unexpected power loss can also cause it. Ensure your drives have PLP.

Regular scheduling of DBCC is essential to monitoring corruption occurrence, the server will not necessarily notice it of its own accord.

Furthermore, a good backup schedule is the best way to avoid needing to mess about with repairs. A full backup every night, plus transaction log backups every 10 or 15 minutes, will get you minimal data loss.

2
  • Thank you so much for this detailed explanation. So we do have daily full backups running and log back ups 30 mins. If I restore the latest backup to prod, it wouldn’t make sense because that backup has the data inconsistency error, right? The backup file is valid though when I queried with restoreverifyonly Aug 18, 2021 at 17:54
  • verifyonly does not do a full checkdb. You would have to work back through your backups to find the most recent one with no corruption. Perhaps you can use that to copy/restore the corrupted row to your live database. Aug 18, 2021 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.