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 |
|
dmaxj
Posting Yak Master
174 Posts |
 Posted - 2010-08-18 : 16:36:37
|
| I am digging into the sp_mergemetadataretentioncleanup proc provided by Microsoft for cleaning up merge replication articles. Our team is in the process of modifying the proc for our production environment. There is a call to sp_getapplock proc from sp_mergemetadataretentioncleanup. We are at a crossroads.Is there any benefit to skipping the call to the sp_getapplock proc or is the call necessary for maintaining merge replication (happens everything 2 minutes)Our problem is that we cannot afford to have a lock on tables over an extended period of time. One DBA says we need it and that we can set the @LockMode arg to 'Shared' - Another DBA says that we do not need the call to sp_getapplock at all.Are there any pros or cons to each approach?Regards |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-19 : 10:04:55
|
| the lock is needed to avoid conflicts arising from simultaneous changestry looking into peer-to-peer transactional replication...maybe that would be more interesting to you...it allows changes on all nodes and you can manage if you want to have conflict detection or not--------------------keeping it simple... |
 |
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2010-08-22 : 01:42:56
|
| So are you saying that the call to the sp_getapplock proc is mandatory when using merge replication ? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-23 : 08:02:33
|
| you're trying to modify a system stored procedure, there is good reason why the sp_getapplock was called withintry the new procedure (without the lock call) in your dev environment and see what happens when there simultaneous access to a rowgood luck...--------------------keeping it simple... |
|
|
|
|
|
|
Merge Replication without locking