SQL Server Performance

Trans Snapshot table locking

Discussion in 'Performance Tuning for SQL Server Replication' started by homebrew01, Apr 7, 2004.

  1. homebrew01 New Member

    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.



  2. satya Moderator

    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.
  3. homebrew01 New Member

    Nope ..... I don't have any identity columns.
  4. satya Moderator

  5. SenthilKumarhk New Member

    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.
  6. satya Moderator

    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.
  7. SenthilKumarhk New Member

    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 ?.
  8. satya Moderator

    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.
  9. SenthilKumarhk New Member

    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"

Share This Page