Replication Question

[ezecastle]

Hi we have had an issue with the MSreplication_subscriptions table. What we're seeing is this:

Occasionally, we will see blocking and deadlocking on inbound replication to our warehouses The deadlocking is on the table msreplication_subscriptions. When we see this, sp_spaceused on that table will show a row count of ~4123123123 when the rowcount should be 145 or so. Doing a DBCC DBREINDEX on the table solves the problem for a period but the problem often returns.

What we did to correct this was drop the Clustered Index and create a non-clustered index exleding the transaction_timestamp. But the issue is the Index reverts back to the clustered index for some reason. No one is recreating the Clustered index but it will be there and we don't know why.

Does anyone know how this would happen or why?

Thanks for your time.

[rmiao]

You changed index on system table? As I know, DBCC DBREINDEX doesn't work on system tables.

[ezecastle]

It's not a system table per se. It is a table created by system resources but it is a standard user table and I have changed the index on it before. When you look at a table it is a type "U" table... That is a user table correct.