Replication: Snapshot files removed after being Generated

  • Article

Snapshot files for a transactional publication are immediately removed after being generated by the snapshot agent

I had recently worked on this issue and decided to post my findings as this may help others that are experiencing the same issue.

Problem:

The snapshot files for a transactional publication are immediately removed after being generated by the snapshot agent.

Environment:

This issue occurred on a SQL Server 2005 environment.

For the distribution database the transaction retention is 8760 hours and the history retention is 168 hours.

Analysis:

When trying to create a new subscriber and attempting to apply snapshot, the following error is generated:

OLE DB Subscriber 'machinename/instancename': {call sp_MSensure_single_instance(N'xxxxxxxxxxxxxxxxxxxxxxx', 10)}

Initializing

Repl Agent Status: 1

OLE DB Subscriber 'machinename/instancename': exec sp_MSset_subscription_properties @publisher = N'machinename/instancename',@publisher_db = N'databasename', @publication = N'publicationname', @subscription_type = 2, @allow_subscription_copy = 0, @queue_id = N'', @update_mode = 0, @attach_version = xxxxxxxxxxxxxxxxxxxx, @queue_server = N''

The concurrent snapshot for publication 'publicationname' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publication

Repl Agent Status: 2

Adding alert to msdb..sysreplicationalerts: ErrorId = 0, Transaction Seqno = 0000000000000000000000000000, Command ID = -1

Now when checking the Snapshot agent, we see that the agent has run successfully. When looking inside the snapshot folder, we see that no files exist even thought they were generated successfully. Looking deeper into the matter - as everything else was / is working fine (no errors), we saw the following:

The status for the publication in the mssubscription table in the Distribution Database has a value of 3 which is wrong. Basically, the status can have the below values:

0 = Inactive.

1 = Subscribed.

2 = Active.

select publisher_database_id, publisher_id, publisher_db,

publication_id,article_id, subscriber_id,

subscriber_db, status

from MSsubscriptions

In my case, there were 146 rows with status = 3

The way to resolve this and to move forward is to remove replication completely (drop publication and drop distribution database) :

KB article: 324401 : How to execute the steps to drop the replication completely.

How to manually remove a replication in SQL Server 2000 or in SQL Server 2005

https://support.microsoft.com/kb/324401

Once the corruption is cleared, and the distribution database and publication recreated, the problem was resolved.