Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beyonder422
Posting Yak Master
124 Posts |
 Posted - 2005-08-05 : 11:13:52
|
| I am about to run a major data purge (could take up to an hour) on a table that is being replicated to and was curious if I might "bunk" out the transactional replication.Would a major delete cause replication to "bunk" out, due to row/table lock, or for that matter, any other reason?How does that work? If a row/table is locked, but then the master is trying to transactionally replicate to it, what happens? Do those trans just get backed up and wait until they can replicate, are they lost, does it kill replication, what? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-10 : 21:59:23
|
we had the same scenario, the delete practically stopped and won't commit for hours on the subscriber's side locking up all other requests on that tablethe only way was to stop the distribution agent and regenerate the snapshotyou could however, divide the deletes into smaller batches to ease off the load  quote:
Originally posted by beyonder422
I am about to run a major data purge (could take up to an hour) on a table that is being replicated to and was curious if I might "bunk" out the transactional replication.Would a major delete cause replication to "bunk" out, due to row/table lock, or for that matter, any other reason?How does that work? If a row/table is locked, but then the master is trying to transactionally replicate to it, what happens? Do those trans just get backed up and wait until they can replicate, are they lost, does it kill replication, what?
--------------------keeping it simple... |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-10 : 23:03:05
|
You may want to do something like this to do the delete in smaller batches. Even if you are not replicating, large deletes can run into problems like filling up your transaction log, failing, and then rolling back for an hour.Notice the 2 minute delay at the end of the loop. You can use this to spread the deletes out over a longer period of time. You can vary the number in the TOP to make the delete batches bigger or smaller.declare @rowcount intselect @rowcount = 1while @rowcount <> 0 begin delete from MyTable from MyTable join ( Select top 10000 bb.ID from MyTable bb where --- Purge Selection Ctiteria --- order by bb.ID ) b on MyTable.ID = b.ID select @rowcount = @@rowcount -- Wait 2 minutes waitfor delay '000:02:00" end CODO ERGO SUM |
|
|
|
|
|
|
|
|
can rowlock/table lock kill replication?