Data Corruption on CDC enabled DB

· Uncategorized

We were recently blessed with data corruption on a system table within a user database. The corruption was 5 days old when we noticed it (don’t ask why) and each process that triggered a checksum failed. Result: no backups more recent that 1 week; page level restore was no option. An additional level of complexity was that the DB had CDC enabled. This is what we did:

  1. Restore DB of 1 week old using the keep_CDC option as a _new DB
  2. Create both capture and cleanup cdc jobs on the _new DB.
  3. Alter the retention option of the cleanup job to 1 minute.
  4. Delete all content from user tables
  5. Copy over all content from the corrupted DB
  6. Launch the CDC cleanup job until all _CT tables were empty.
  7. Alter the retention option of the cleanup job to 7200 minutes (the default).
  8. Rename corrupted DB to _OLD
  9. Rename the _new DB to the original name
  10. Run DBCC FREEPROCCACHE

This last step was needed for stored procedures that were cached and still refered to the corrupted DB.

Life can be fun.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: