asked
data loss after clustered index rebuild in sql2012
btw, the clustered index is not the primary key..
can you please tell clearly what kind of data loss has happened ? What was the datatype of column ?
Can you tell us more about the data loss? How have you confirmed it? Was it only a row?
It doesn't matter if the clustered index is not the primary key, it can also be a non-unique index (not advisable). Because the clustered index is actually the table, any damage to this index = any damage to your table. To my opinion, your problem can only happen if there was already some corruption in this table (file?) Did you get any information from DBCC (before the rebuild?)
We still don't know how the data loss was discovered/confirmed. If it was only based on size then a rebuild may have reduced pages. The OP needs to provide more details so we can focus our efforts in assisting.
Make sure this problem is not caused by an application/user. I had a similar issue when restoring an Axapta 2012 database. After the restore when we started an Axapta service, this service was doing a sync, resulting in removing several records. But before we discovered this, I had to explain several times I did not restore a corrupt backupfile. In the end we could reproduce this behaviour and fixed it.
Just reinforcing what others have said, there really is no way that an index rebuild resulted in data loss without there being a corruption issue on the database. If your database passes a CHECKDB run through DBCC, then the data loss has to be from other sources.
As wilfred has also told, I suspect corruption. Please run DBCC CHECKDB and follow suggestions as mentioned in the link:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6a89fdb-d487-43a2-a519-5d3e8b9fe5f2/database-corrupt?forum=sqldatabaseengine
As a counterpart, this data loss could also happen because somebody ran a DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, in order to fix corruption ...
17 People are following this question.
