question

tencore avatar image
tencore asked

data loss after clustered index rebuild in sql2012

btw, the clustered index is not the primary key..
clustered-indexrebuild
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image Venkataraman commented ·
can you please tell clearly what kind of data loss has happened ? What was the datatype of column ?
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Can you tell us more about the data loss? How have you confirmed it? Was it only a row?
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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?)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
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.
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
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.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
Venkataraman avatar image
Venkataraman answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image Wilfred van Dijk commented ·
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 ...
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.