Transactional replication – The subscription status of the object could not be changed. (Source: MSSQLServer, Error number: 14068)

Recently I’ve faced a general error on SQL Server transactional replication – The subscription status of the object could not be changed. This was popped up on no matter what changes trying to implement – add a new subscriber, drop a subscriber, add or remove any article, etc. In other words – the replication became something like read-only.

The changes I tried to implement using SSMS as well as system procedures, like “sp_dropsubscription”, but with no luck: The subscription status of the object could not be changed. (Source: MSSQLServer, Error number: 14068). Other errors by trying other procedures I’ve got:

Msg 14068, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1386 [Batch Start Line 0] The subscription status of the object could not be changed.

Replication Monitor could not reinitialize one or more subscriptions that support automatic initialization. Additional Information: An exception occurred while executing a transact sql statement or batch. (Microsoft.SQLServer.ConnectionInfo)

The subscription status of the object could not be changed. Could not update the distribution database subscription table. The subscription status could not be changed. Changed database context to “TestDatabase”. (Microsoft SQL Server: Error 14068) 

Even a snapshot agent was failing without saying anything useful… Then it looked like I need to re-setup a replication from scratch. This wouldn’t be something much if only one publication on a server with few articles, etc. But in my case – there are over 10 publications with more than 100 subscriptions. All the servers are in production and to re-setup replication – would cost a lot…

Fortunately, I’ve found a way to fix the replication without re-setup. Here what I found.

First, I’ve checked is there any orphaned articles in the replication? This might be done by running the script:

use [database]
go

select object_name(objid), name, artid
from dbo.sysschemaarticles

Success! I’ve found one article exists as a replicated object (in my case udf function), but already dropped out of the database:

Please note, you can’t simply re-create a dropped article since it will get different object_id and will not fix the issue (just now though, instead of deleting the entries as described below, you may try to update article id, but at the moment I was fixing, didn’t thought about this option).

The next step – clean this up. It’s as easy as to delete a record out of a table. For sure, you need to know from what tables. Here you go, four tables in total: syssubscriptions and sysschemaarticles in a publisher database, and MSsubscriptions and MSArticles in distribution database. In my case, from the query above I’ve identified that artid = 2 is the candidate to drop. Before deleting, I’ve copied the entries to a backup table in case I’ll screw up something else and need to restore the change. Remember, all the things I was doing are on production environment…

use [database]
go

delete
from syssubscriptions 
where artid = 2

delete
from sysschemaarticles 
where artid = 2

delete
from distribution.dbo.MSsubscriptions 
where article_id = 2

delete
from distribution.dbo.MSArticles  
where article_id = 2

Success again! After the orphaned article has been deleted out of replication, now I can do all the changes again – add a new subscriber, add a new article, etc. All back to normal!

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *