0

Occasionally when creating and finishing a snapshot to re-sync data, some of the subscribers seem to hang about for several hours afterwards before they start to take the snapshot. There's no blocking that I can see on the distributor. When I check on the subscriber for sessions against the subscription database, they are running: sp_MSreleasesnapshotdeliverysessionlock

This sometimes happens on a number of SQL Server versions, but all patched to recent CUs. What is this process? And what is likely to be going wrong?

2

It's important to understand where is the bottleneck...

Are the BCP-files for the snapshot actually getting created? Are you using PULL or PUSH subscriptions? Remote or Local DIST? Can you setup a Profiler Trace or XE Session on each component...PUB, DIST and SUB and trace activity...plus maybe look at wait-stats and what SQL is waiting for? Turn on Verbose-logging for LogReader-Agent and Distribution-Agent as well. Query the History Replication Sys-Table to get additional information too... sp_helptext on sp_MSreleasesnapshotdeliverysessionlock - what is in it?

1

Not sure if this helpful or not, but that sp is a built in sproc within the Master database you can look at the code within it.

The tag line is:

This procedure is used by the distribution/merge agent to release the application lock acquired through sp_MSacquiresnapshotdeliverysessionlock.

Looking at the second 'aquire' stored procedure that creates a snapshot delivery progress table, wondering if this process is taking some time?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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