Delete virtual subscription metadata from Distribution database

Question

Can anyone please help me to delete virtual subscription metadata from Distribution database? Somehow distribution cleanup job are not deleting them.

Thanks,

---

Kindly mark the reply as answer if they help

Answer 1

It should not be deleting them as they are required to quickly initialize new subscriptions - you might want to change your publication type to native. IE

sp_changepublication 'publication','Immediate_Sync', false

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

To clean those up you can run the following but I also recommend what Hilary suggested in removing the immediate sync option on your publication. 

On Distributor

 DELETE FROM [distribution].[dbo].[MSdistribution_agents]
 WHERE subscriber_db = 'virtual'

 DELETE FROM [distribution].[dbo].[MSsubscriptions]
 WHERE subscriber_db = 'virtual'

On Publisher Database

 DELETE FROM [dbo].[syssubscriptions]
 WHERE dest_db = 'virtual'

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 3

Chad - this seems to work, but I don't think it is the best idea. Besides you will be deleting one row per publication/article which will not amount to too many rows and will likely not impact space reduction significantly.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Thanks Hilary.
I have done following which has resolved this issue.

1. To find out such virtual subscriptions run following query on distribution database:
select * from MSsubscriptions where subscriber_db = 'virtual'

2. To find out such publications run this TSQL on publisher on publication database.
select * from syspublications where immediate_sync = 1

3. Now changed this option using:

EXEC sp_changepublication
@publication = 'publication name',
@property = 'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'publication name',
@property = 'immediate_sync',
@value = 'false'
GO

After running these command I verified changes into syspublications table on publisher. Once changes are verified I ran the Distribution clean up agent job and this time it cleared the unnecessary metadata and leaving the distribution database size to reasonable.

Cheers!!

---

Kindly mark the reply as answer if they help

Answer 5

Sunil - glad it worked out for you.

Chad, I tried to generate a new snapshot for a new subscriber after following your instructions to delete the virtual subcribers.

I got the below error message.

So, I don't think we should delete rows directly from MSDistribution_agents, MSSubscriptions, or syssubscriptions.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941