Concurrent snapshot still locking tables for a long time

Hot Topic

Date string conversion similar to Excel's Date(year,month,day)
Image in Page Header
Cursor approach versus T-SQL Set based solution--Avoid/Eliminate Cursor if possible
What is the ADO DataType for nVarchAR(max)
Cant See Network Server
Im a beginner: Error Description: [DBNETLIB][ConectionOpen (Connect()).] SQL Server doesnt exist or the access was denied.
Converting a type into another one
SELECT in a table takes FOREVER
SQLConnect to SQL Server fails when connection intialized from a service
Convert Access SQL to ANSII compliant SQL

SQL Server

Error importing data from oracle database to an SQL database
What software can generate the insert script
Problem with CLR SP connecting to remote server using VPN
a lot of exception were output when I debug
How to attach in SQL 2005
Excel 2003 Query Performance
How to query xml data in column type xml
SQL Server Diagrams?
References in SQL Server Projects
Line positions

Concurrent snapshot still locking tables for a long time

We have transactional replication running with a seperate publisher/distributor/subscriber. I want to add a couple of articles to the publication, and then initialise them. I have added the articles and run sp_refreshsubscriptions

I now want to refresh the subscriptions. I have selected not to lock the tables on the snaphot tab of the publication properties, but whenever I run the snapshot agent it locks the application solid! Its odd, as soon as I run the snaphot agent, the phones start ringing within minutes. The application is Great Plains and I have set the snapshot agent to run nightly anyway.

Is there any way I can run the snapshot agent during working hours to refresh this one article Once I have successfully done this, I have a number of articles want to add - but I can't lock the tables when refreshing the initial snaphot.

Answer this question

Concurrent snapshot still locking tables for a long time

  • mikeymay

    If the snapshot agent is holding locks for an extended period of time even while it is generating a concurrent snapshot, chances are it is blocked by other on-going activities at the publisher. The "Do not lock table..." description in Enterprise Manager is slightly misleading as the snapshot agent does take out schema-mod locks on the published tables briefly at the beginning as well as at the end of generating a concurrent snapshot so chances are good that the blocking you saw were related to those. From the sound of it, it looks like you have the immediate_sync property set to true for your publication which tells the snapshot agent to generate snapshot for all articles every time it is run so subcriptions added in the future will have snapshot data readily available for initialization in the future. You can try changing the immediate_sync property of your publication to false so snapshot will only be generated for articles with subscriptions requiring initialization. (sp_refreshsubscriptions may reinitialize subscriptions on more than the article specified based on the fk relationships you have)

    Hope that helps.

    -Raymond
  • Concurrent snapshot still locking tables for a long time
Answer this question