SQL Error in the Post Snapshot File for Transactional Replication

When using replication, it is sometimes useful to run a set of commands on the subscribing database either before or after the initial snapshot has been applied. By using a pre-snapshot file, a DBA could remove any dependencies in the subscribing database that would keep the table from being dropped in the event of a re-initialization. We use post-snapshot files to create indexes on the subscribing database that do not exist on the publisher. To find out whether or not pre/post snapshot files are being used, right click on the publication and choose properties. From there, select snapshot and look under the “Run additional scripts” section.

 

So when the subscription is initialized, a snapshot is prepared and placed in the default folder. Along with the bulk copy files, any pre or post snapshot files you are using are placed in there too. So the snapshot is applied to the subscriber and, in this case, a post-snapshot script is ran against the subscribing database. While the snapshot and the post-snapshot scripts are being ran, updates to the replicated tables are being queued for replication in the distribution database. These will be replicated to the subscriber once the snapshot has been applied and the post-snapshot script has completed.

But what happens in the event that the post-snapshot script has an error? The distribution agent considers this to be a failure and retries the post-snapshot script over and over again. While this is happening, updates that are queued for distribution are not being applied to the subscriber. How can we fix this?

One option is to open the post-snapshot file at the location under “Run additional scripts”, fix the error, reinitialize the subscription and re-create the snapshot. This might not be the best option depending on the size of your snapshot and the time it takes to generate it.

The second option is to directly open the post-snapshot file that was copied into the folder when the snapshot was created. By editing the post-snapshot file in the folder specified in the “Put files in the default folder” option, we are directly modifying the SQL that the distribution agent is executing and failing on. If time is critical, you may want to remove all SQL from the file and save it. Note: If you do this, you will have to manually apply the SQL to the subscriber. Once the error in the file either is fixed or the file is cleaned out, the distribution agent will open the file and complete successfully. After that, all the queue updates, inserts and deletes can flow from the publisher to the subscriber.

Remove an Article from Transactional Replication without dropping the Subscription

          While using Transactional Replication, there may come a time when you need to drop an article from a publication, a publication that may have one or more subscriptions. You would think that the process would be very similar to adding an article to the publication; open up the article and uncheck the box. Now, if there were no subscribers to the publication, this would not be an issue, but odds are that this publication has been used for some time and there are one or more subscribers to it. If this is the case, when you go to uncheck the article to remove it, you may receive an message stating that since there are subscriptions on this table, you will have to reinitialize this subscription and generate a new snapshot.

          So how can we get around re-initialization of the subscriber and new snapshot generation? We manually execute some of the replication stored procedures to remove the article and keep the snapshot from being invalidated.

First we must use sp_dropsubscription to remove the subscription to the individual article.

EXEC sys.sp_dropsubscription

    @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @subscriber = '<SubscribingServer>',
    @destination_db = '<DestinationDatabase>'
 
Next, we drop the article from the publication without invalidating the snapshot. We do that by executing sp_droparticle with the force_invalidate_snapshot set to 0.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @force_invalidate_snapshot = 0