Freigeben über

"Virtual" subscription entries in the Distribution..MSsubscriptions table

  • Artikel

If you are using Transactional Replication, you may find entries in your Distribution..MSsubscriptions table which have subscriber_id= -1 and -2. Also, the subscriber db will be set to "virtual"
You may find that for some of your Tran Publication, there are no "virtual" subscription entries while it is there for others.

Reason:
Whenever any one of "allow_anonymous" or "immediate_sync" properties of a publication are set to TRUE, these entries are added.
These entries are as such harmless and are added to signify that this publication can have ananymous subscriptions.
In one particular case, these entries were found to be linked with thousands of records into MSrepl_transaction and MSRepl_commands. Since these entries are not removed by distribution cleanup agent, they were causing some performance isssues.
But this is not true in every case and you should check if this is the case in your environment.

If you want to turn off (delete) these entries, you should recreate your publication with "allow_anonymous" and "immediate_sync" properties set to FALSE. If you don't want to recreate publication, you can try sp_changepublication to set these to false. But in my testing, sp_changepublication did not help and and recreating publication helped remove the entries.
If you create the publication using scripts, you should look for them on the sp_addpublication command line.

Comments