Replication is not cleaning up after itself - is_non_sql_subscribed in sys.columns

• Elephant

  Mr or Mrs. 500

  Points: 511

  More actions

  February 16, 2010 at 9:03 am

  #219212

  Hi,

  To geve you an idera of what i am trying to do.

  I have been trying to alter a Table that is being replicate by a transactional Repl and a SnapShot Repl (to different server).

  I have dropped the Subscription to all server, Drop the Publication but the Replication is still locking the Table as it beleive it is being replicated.

  i can see that the replication still has the column selected by the Snapshot Replication set for replication (Select is_non_sql_subscribed,* from sys.columns Where object_id = 1882489785)

  I have re created the Snapshot repl and try to alter it column by Column to no Succes.

  try tis too.

  what is described in the following Article

  http://blogs.msdn.com/sqlserverfaq/archive/2009/10/01/cannot-alter-schema-of-replicated-article.aspx

  Except the starting SQL in Single mode a I have several Live Website on the Box an i'll get shot if stuff go down.

  The other option i have would be to restor the Db over itself loosing the replication setting then recreating all the replication (easily scripted) but it will cause me at least 1H down time and all that i could get away is 10 min at 4AM...

  Has anyone got any suggestions?

• Elephant

  Mr or Mrs. 500

  Points: 511

  More actions

  February 19, 2010 at 9:53 am

  #1121967

  Hi, I have found a soft solution to the problem

  I have:

  - Created a Transactional Publication (the origianl was Snapshot) for the Table and Columns that where locked.

  - Added a subscription to a test Db

  - Initialised it

  - Drop the subscription and Publication

  and this clean up the Sys.columns .... so virtualy no down time 🙂