sp_changepublication allow_anonymous unable to complete

  • I was trying to add an article to current transnational replication and don't want to recreate the whole snapshot so I ran below script to change the publication properties allow_anonymous and immediate_sync, but it was stuck and unable to complete even over 30 minutes, the database is not big, only 30GB. I have tried recreating the whole publication then run it again, but no luck.

    sp_changepublication @publication = ‘BIDB’,

    @property = ‘allow_anonymous’,

    @value = ‘false’

    GO

    EXEC sp_changepublication @publication = ‘BIDB’,

    @property = ‘immediate_sync’,

    @value = ‘false’

    GO

    what's going on with changing these 2 properties? and it was blocking other normal user sessions, have to cancel and rollback with 'ROLLBACK TRANSACTION' as described here:

    https://social.msdn.microsoft.com/Forums/en-US/201a4c22-db83-402e-a99f-8bdbf119cd12/exec-spchangepublication-long-time-to-be-executed?forum=sqlreplication'

    has anyone come up with this before? thanks!

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply