0

SQL Server 2008 R2, an update corrupted my table somehow, likely to do with the storage subsystem. I am on a dev database, so aren't overly concerned about losing data, it isn't business critical. But I am writing a set of queries which will eventually be used on prod and the corruption happened in the middle. As I don't want to lose the previous changes (they took a while to complete), I decide I can run DBCC CHECKTABLE with the REPAIR_REBUILD option. Sure, restore from backup would normally happen on prod system, but I am on dev here and backups are virtually non existent. Anyway...

According to the spec here, this should be no problem, data will not be lost. It does state "Use as a last resort", but this appears to be mostly for constraint reasons. My table has zero constraints so it seems like this doens't apply here. But just to be safe, I wrap the statement in a transaction, ready for rollback.

I set the database to single user mode, then run the DBCC statement. A few queries work, data looks about right, so I committed the transaction.

However, on further investigation, the statement has done as it states - there is no data loss. But it has ADDED rows to the table. The rows are identical to existing rows and will require a delete to remove them. As fas as I can see, this is not stated in the msdn article above.

For me, this counts as data loss - I haven't technically lost rows, but particular rows have been altered from their original values. Namely - rows that did not exist before do now. Again, this isn't technically loss but it does constitute a change to the data which will cause queries to return different values (imagine SUM or COUNT queries).

Does anyone have any ideas how or why SQL Server would decide adding additional rows are fine?

blobbles
  • 1,586
  • 10
  • 16
  • Paul Randal has a great blog post about misconceptions about using Repair. http://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/ The most important thing to note from this as well as from the link that you provided is that Repair_Rebuild is really only good on indexes that have no possibility of data loss (AKA nonclustered indexes). Your post does not make it clear that you are trying to repaid nonclustered indexes, and it makes me believe you are probably trying to repair a clustered index. If so, it will not fix this the way you are hoping it will. – mskinner Nov 27 '15 at 16:55
  • Thanks mskinner, the blog post pretty much confirmed the research I had done around it anyway. However, my table is a heap, not a clustered index. It has 2 nonclustered indexes on it which appeared to not be affected by the corruption. Maybe this is something we should be aware of with this command - it can add rows. – blobbles Nov 28 '15 at 22:07
  • Hey blobbles, if they corruption was on the heap then you can look at that as a clustered index from this perspective, since in both cases it is the actual data of the table. And I think the real key is that if you are running repair on the actual data of the table (Heap or Cluster) bad things can happen! I had never seen it add rows but it doesn't shock me. Thanks for the follow-up very interesting scenario you had here. – mskinner Nov 29 '15 at 23:07

0 Answers0