How to find rows marked for replication?

[sqlcasey]

SQL Server 2005

Someone here attempted to update about 3000 rows in a table that is in replication (on the publisher side). We are using push replication.

The rows were updated successfully on the publisher table, but for some reason, it seriously locked up the replicated table on the subscriber side. Because of the locking problem, none of the rows actually got updated on the subscriber side.

At this point, the DBA turned off replication to release the locks on the table (which it did).

So, now the situation is, there's a bunch of rows marked for replication that haven't yet been successfully replicated. My question is, is there a way to "undo" the changes? If so, how would one do this.

The other option is to turn replication on later tonight when no users are online, I suppose.

Has anyone else had this problem and how did you solve it?

Thanks

 

[ptheriault]

I have never had that problem but replicated transactions can be found at the following location.
There is a database called distribution. In that db look for a table called msrepl_commands. You could truncate that table.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Also, you might be better off at this point taking a new snapshot.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlcasey]

Yes, I see about 3500 rows in msrepl_commands

Are you deleting them won't hurt anything?

How would I apply a snapshot under these conditions? We have never applied snapshots to any tables here.

Thanks much

 

[ptheriault]

If you truncate the msrepl_commands table then your subscriber will never receive those commands. If that is what you want then "no" it won't hurt anything.
If you need those commands then you have two options.
1st option like you already stated is to wait till tonight and turn replication back on.
OR
2. truncate the msrepl_commands table and re-snapshot the subscribtion. (which you should also do off hours)
When you created replication it would have created a snapshot of each article in the publication and bulk copied the data to txt files then it would copy those file to the subscriber where they are applied. When you take a snapshot it locks every article that is a memeber of the publication.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

If you right click the publication (I think) and go to properties, there's an option somewhere that says "reinitialize subscription". I always click this before re-running the snapshot (which should be on the same tab). This way it clears everything properly.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"