Transactional Replication Snapshot Stuck RRS feed

  • Question

  • Snapshot Agent History shows:

    [98%] The process is running and is waiting for a response from the server.

    Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


    The replication used to work just fine. There are no blocking processes. I've increased the timeout period via the agent profile to 1 hour. There's plenty of space in the snapshot file share.


    I can see a long running query from the Snapshot Agent SMO Connection "application" in the Activity Monitor. It shows this query:

    (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000))SELECT
    SCHEMA_NAME(tbl.schema_id) AS [Table_Schema], AS [Table_Name], AS [Index_Name], AS [Name],
    CAST(p.value AS sql_variant) AS [Value]
    sys.tables AS tbl
    LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
    LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
    INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
    LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
    INNER JOIN sys.extended_properties AS p ON p.major_id=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN i.object_id ELSE k.object_id END AND p.minor_id=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN CAST(i.index_id AS int) ELSE 0 END AND p.class=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN 7 ELSE 1 END
    [Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[Name] ASC

    I've traced that query and the parameters passed in are '0' and '0'. Running the query in a query editor window with the parameters indeed exhibit the same results - a long running query that never finishes. The number of logical reads will surpass the billions and continues climbing until it times out. I am 100% sure it's this query that's causing the problem and cannot work around it since the query is internal to the snapshot process - narrowing it down to the ugly case statements in the joins. Is there any way I can get around this? Might be a bug. I may have the option to upgrade to CU20.

    Currently on SQL Server version 14.0.1000.169

    Wednesday, June 24, 2020 9:35 PM

All replies

  • Hi ElvisVangDBA,

    This error usually is caused by QUERY timeout or CONNECTING timeout. 

    Query timeout: The reasons a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the query is running.  The BlkBy column will show the SPID of the blocking connection if the query is blocked.  For slow running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed.  For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics. 

    The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in menu would open below window where the value can be set, if needed.

    More details, you can refer to: SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation

    And here is a similar thread may help:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

    Hope it will help

    Best Regards


    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

    Thursday, June 25, 2020 3:11 AM