Drop subscription locking users

Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
|||The new publication is on the same server, same database, the subscriber is a new server, so in essence your assumption is correct. I want to drop the old publication on the existing publishing server/database, since the subscribing server will be retired.
Ok, so I need to drop my articles on this publication one by one. Ugh! That's 1600+ articles.
Thanx for the answer.
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:%23KCcOfJ5FHA.2432@.TK2MSFTNGP10.phx.gbl...
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!