2

I have snapshot replication set up on a server which publishes to a second server as part of a scheduled SQL Server Agent job. When the snapshot finishes, I would like to detect when each table has been updated or when all tables have been updated.

The best solution I have found so far is to put a flag into a placeholder table before the SQL Server Agent job which is replicated to the second server and then have a job on the second server that runs every minute to check to see whether a new snapshot has arrived.

Does anyone have a better solution or a way to identify when a snapshot has been processed onto a new server?

1
  • 2
    How often do you snapshot. It will be better to use Transactional replication and schedule it to run as pee your needs. This way, you don't end up doing lot of disk I/Os and locking on the publisher db.
    – Kin Shah
    Commented Apr 24, 2014 at 12:37

3 Answers 3

1

Change it to a pull subscription, and watch for the SQL Agent job to stop. When it stops the snapshot has been applied. Or you could get really creative and setup a linked server to the distributor if you want to keep it as a pull subscription and watch for the job on the distributor to stop.

0

You might be able to find something useful in Extended Events. Good luck.

0

If this is the only subscriber for the snapshot, you could have it execute a script after it has been applied. See Books Online.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.