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. 