SQL Server Performance Forum – Threads Archive

Trans Snapshot table locking

From what I’ve read, SQL2000 shouldn’t lock tables during the initial snapshot for transactional replication, I even checked the option : "Do not lock tables during snapshot generation. All Subscribers must be servers running SQL Server version 7.0 or later." However, running the initial snapshot last night DID lock tables needed by other processes. Am I missing something, or is Microsoft lying ? Help ! I can’t be limited to running snapshots on the weekend.

After replication begins, the Snapshot Agent places shared locks on the publication tables. The locks prevent changes until a record indicating the start of the snapshot is entered in the log file. After the transaction is received, the shared locks are released and data modifications at the database can continue. The duration for holding the locks is very brief (a few seconds) even if a large amount of data is being copied. Have you got any columns enabled with IDENTITY property? Articlehttp://www.sql-server-performance.com/snapshot_replication_tuning.asp for your information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Nope ….. I don’t have any identity columns.

Review the tuning article link referred above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I do have same problem. I have click the check box "Do not lock tables during snapshot generation", still the sql server locks the whole database during snapshot generation.

In general, you shouldn’t try to rely on the locking behavior in you application. You CAN, however write this behavior into your application. Most of the time tran is the better solution. Snapshot is easier to administer if the repl frequency/volume is low You can’t create a snapshot in snapshot replication without table locks because there is no mechanism to trap changes to the articles. You may use a @sync_type = ‘none’ option which wouldn’t have locks. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I am using Transactional replication. In Publication Properties – Snapshot folder, I have checked "Do not not lock tables during snapshot generation. All Subcribers must be servers running SQL Server version 7.0 or later". During initial snapshot generation the sql server places shared locks all tables. I mean there is no difference in check or uncheck this option. I prefer to use transaction replication, My application is 24 hours non-stop, there is no way to suspend service. I have read through & follow the guidelines "Concurrent Snapshot Processing". I don’t have any Identity column or text column. Do I missed anything else or this option does not working ?.

By design it will adopt shared lock while snapshot generation and run sp_helppublication and check the compress_snapshot column to be sure that the option is right. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Finally I figure out what to do !. "Allow Pull Subcriptions" should be unchecked in Subcription options tab screen of publication properties. Note: Pull Subcription does not support "Concurrent Snapshot Generation"

]]>