SQL Replication - Monitoring Tips When Delivering Snapshots for Transactional Publications

Monitoring the delivery of a snapshot for Transactional Replication can be a bit tedious, especially if it is a large publication or you are delivering large tables.  However, the more you know about what to expect, the less likely you are to panic if things don't quite seem to be going the way they should.

The key steps in snapshot delivery are:

• Run PRE scripts (drop the existing published table/article on the Subscriber)
• Run SCH scripts (recreate the published table/article on the Subscriber)
• Push BCP files (BULK INSERT the raw data from the published table/article)
• Create Indexes
• Push queued transactions (technically this step is post snapshot delivery)

Monitoring the first three steps is pretty simple as Replication Monitor gives you plenty of feedback during this process.  Unfortunately, index creation does not report any progress (and you may even get the dreaded message "The replication agent has not logged a progress message in 10 minutes" if it takes a while to create the indexes).

Note: If you get that message a lot and you know it's rogue (i.e. the Distribution Agent is still running and there is no blocking of the ALTER TABLE or CREATE INDEX processes on the Subscriber) you can change the heartbeat interval on the Distributor by following the instructions here (Thanks Amit!): http://troubleshootingsql.com/2010/05/28/replication-agent-has-not-logged-a-message-in-10-minutes/  We set ours to 20 minutes by running this script on the distributor:

EXEC sp_changedistributor_property @property = N'heartbeat_interval', @value = 20

By default, SQL Server will only create the clustered indexes for tables pushed to the Subscriber.  When the clustered index creation process is complete, the Distribution Agent will report "Delivering replicated transactions".  If you are using the default settings (i.e. only recreating the clustered indexes at the Subscriber), this message means exactly what is says, queued transactions are now being pushed to the Subscriber.  

This message always appears after the clustered indexes are created.  Thus, if you also replicate the non-clustered indexes (like we do in our environment), this message means that the creation of the non-clustered indexes has started, not that it is actually delivering the queued transactions.  In some cases, the Distribution Agent may even report that it "Delivered snapshot from the unc\{path} sub-folder in ##### milliseconds" before the non-clustered index creation is complete.  The only way to know for sure is to either query the Subscriber or wait for the Distribution Agent to report ### transaction(s) with ### command(s) were delivered in Replication Monitor.

There are just a few nuances to monitoring replication and I hope that this helps to shed some light on the process.