21854 Error

Question

OS: Windows server 2008 R2
SQL: Windows server 2008 R2

I deleted all publications from this server, and then I try to create a publication, get a error message as below:
Error: 21854, Severity: 10, Could not add new article to publication ‘%s’ because of active schema change activities or a snapshot is being generated.

How can I resolve it?

Best Wishes,

Answer 1

I have a copy of the resource database, so that I can search also code for system procedure. I found that this error is raised in sp_addarticle:

        -- Get applock to co-ordinate with snapshot agent. Basically,  
        -- we want to prevent a new article from being added while a   
        -- snapshot is being generated.  
        EXEC @applock_result = sys.sp_getapplock @Resource = @publication,  
                @LockMode = N'Exclusive',  
                @LockOwner = N'Transaction',  
                @LockTimeout = 0,  
                @DbPrincipal = N'db_owner'  
  
        IF @applock_result < 0  
        BEGIN  
            RAISERROR(21854, 16, -1, @publication)  
            GOTO UNDO  
        END  

It seems that there is another operation running on the publication which also has taken this application lock.

I don't whereelse this application lock may be taken, but one possibility would be to set the database offline and then online again to roll back all open transaction.

You could also look for processes with this application lock, but I'm not sure if it is worth the effort.

Answer 2

Hi @zhang jay ,

Whether the publications were deleted correctly. And try to restart the SQL Server service.

https://learn.microsoft.com/zh-cn/sql/relational-databases/replication/publish/delete-a-publication?view=sql-server-ver15#using-transact-sql