none
Query timeout expired. Cannot release the application lock "snapshot_delivery_in_progress" because it is not currently held RRS feed

  • Question

  • SQL Server 2012 sp3 installed on two Windows 2012 servers.

    SQL Server Replication set up to replicate from one database server to the other.  We get the following errors in Replication Monitor:

    Query Timeout Expired.

    The process is running and is waiting for response from the server

    Cannot release the application lock (Database Principal: 'db_owner', Resource 'snapshot_delivery_in_progress_dbname') because it is not currently held.  (Source:  MSSQLServer, Error number: 1223)... we continuously see the message while Viewing tye Suynchronization Status:  "Skipping file 'table_name_16.pre' because it has already been delivered for a previous article or by a previously interrupted snapshot'   and then we get the query timeout and the error number 1223 again.

    No data is getting transferred from distributor to subscriber.  Tracer Token in Replication Monitor shows trace from Publisher to Distributor in < 1 second but from Distributor to Subscriber NEVER COMPLETES.   We have been allowing this to run for over 4 hours without a single record getting replicated.

    Any help would be appreciated.

    Thursday, December 14, 2017 4:08 PM

All replies

  • Hi sqlserver_dba_developer,

    >> Skipping file 'table_name_16.pre' because it has already been delivered for a previous article or by a previously interrupted snapshot

    It seems you’re working with transactional replication, right? Try to re-initialize the subscription see if it fixes the issue.

    >> SQL Server 2012 sp3 installed on two Windows 2012 servers.

    Please install the latest service pack SP4 for SQL Server 2012 which may improve the overall performance.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Hannah Yu Monday, December 18, 2017 10:37 AM
    Friday, December 15, 2017 4:54 AM
  • hi, try to change the distribution agent profile -QueryTimeout to a higher value. let say 9600.. and make sure your replication monitor application is not set to "Automatically refresh the status.." refresh it manually. And use sp_whoisactive to see what's running on your distribution and subscriber db.

    re-initializing the replication is your very last resort.


    Regards, Dan


    • Edited by codandn Friday, December 15, 2017 5:45 AM edited
    Friday, December 15, 2017 5:44 AM
  • Is it worked fine previously or 

    is this the first time you are ruining initial synchronization. 

    How large is your database. and which replication method you are using to replicate data to distributor  to subscriber.

    I mean is it transaction replication or merge replication and are you replicating 1:1  all data or replicating some data with filters. 


    පුෂ්පකුමාර

    Friday, December 15, 2017 6:10 AM
  • as already suggested try to increase QueryTimeout on Distribution agent profile it may that table that is being delivered is very big and it needs time.

    But also check if there are any locks on subscriber that can prevent accessing some tables. as far I see some tables been already delivered and it is just stuck on other.

    Friday, December 15, 2017 10:07 AM