SQL 2016 Replication problem with compatibility level RRS feed

  • Question

  • I have three server SQL 2016 SP1 CU5 Developer edition. On this servers I have configured transaction replication. One server I use for distributor, one for publisher, one for subscriber. All database have compatibility level 130.

    When I want to create replication and after I start Snapshot Agent I got this:

    I tried recreated replication many times, but always I got this message and replication never start.

    But when I change compatibility level on my publisher database to 110, Snapshot Agent finished and replication started.

    What is wrong with compatibility level 130 and how can I repair this?

    This is error for Prefetch objects failed for Database 'DB':

    Error messages:
    Source: Microsoft.SqlServer.Smo
    Target Site: Void PrefetchObjectsImpl(System.Type, Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
    Message: Prefetch objects failed for Database 'DB'.
    Stack:    at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type objectType, ScriptingPreferences scriptingPreferences)
       at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.ObjectPrefetchControl.DoPrefetch(Database database)
       at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.PrefetchObjects(ObjectPrefetchControl[] objectPrefetchControls)
       at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoPrefetchWithRetry()
       at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
       at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
       at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
       at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
       at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
    Get help: http://help/0
    Source: Microsoft.SqlServer.ConnectionInfo
    Target Site: System.Data.SqlClient.SqlDataReader GetExecuteReader(System.Data.SqlClient.SqlCommand)
    Message: An exception occurred while executing a Transact-SQL statement or batch.
    Stack:    at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
       at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
       at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
       at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
       at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
       at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
       at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
       at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting)
       at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjects(ScriptingPreferences options, IEnumerable`1 filters)
       at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type objectType, ScriptingPreferences scriptingPreferences) (Source: Microsoft.SqlServer.ConnectionInfo, Error number: 0)
    Get help: http://help/0
    Server DB, Level 11, State 0, Procedure , Line 0
    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Source: MSSQLServer, Error number: -2)
    Get help: http://help/-2
    Target Site:
    Message: The wait operation timed out
    Stack:  (Source: <Unknown>, Error number: 0)
    Get help: http://help/0

    Tuesday, October 10, 2017 7:19 AM

All replies

  • This is a generic timeout message. It probably means that you are experiencing blocking on the publisher. You will need to either use the initialize from backup method or try to generate your snapshot at a period of low user activity.
    Tuesday, October 10, 2017 2:58 PM
  • I don't have any blocking on the publisher. I started with my Snapshot agent at a period when there is nobody on the servers. If I have compatibility level on publisher database 130 I have this message, but If I change compatibility level to 110, I don't have any error and all is OK.

    What is wrong whit compatibility level? It is possible to have some wrong type of columns?

    Tuesday, October 10, 2017 8:37 PM
  • I am suspecting a problem with statistics. Did you update statistics before running the snapshot?

    SQL 2014 has a new cardinality estimator and some users have experienced problems with it until they change the collation. I suspect you are running into the same problem.
    Tuesday, October 10, 2017 8:42 PM
  • First I updated statistics and then run Snapshot Agent. But problem is same.
    Wednesday, October 11, 2017 12:07 PM
  • what sync_method is used in your publication?  (check syspublications on publisher) sync_method=3 (concurrent- default) sync_method=5 (database snapshot). I had issues with locking and issues with snapshot generation when moved to 2014, after changing this to database snapshot it works much better. You may try to change that. (sp_changePublication) you cant have any active subscription while doing that change:/
    Wednesday, October 11, 2017 12:53 PM
  • Bartosz,

    I have sync_method=3. When I change sync_method to 5 I got same error.

    Wednesday, October 11, 2017 8:35 PM
  • Can you change your heartbeat interval to something large?

    USE master
    exec sp_changedistributor_property
      @property = N'heartbeat_interval',
      @value = 30;

    Wednesday, October 11, 2017 8:40 PM
  • Hilary,

    I changed heartbeat interval, but error is same.

    Thursday, October 12, 2017 9:40 AM
  • how long it takes before it times out?

    Did u try different Agent profile for snapshot? (change QureyTimeout and LoginTimeout parameter to higher values)

    Thursday, October 12, 2017 9:45 AM
  • Hi Bartosz - LoginTimeout controls how long the replication agent will try to connect to the sql server. I don't think it will be a factor here - querytimeout definitely will come into play:)

    Can you run the snapshot agent from the console with OutputVerboseLevel 4 to see what shows up in the screen?

    These problems again are symptomatic of blocking on the publisher/distributor.

    Thursday, October 12, 2017 9:50 AM
  • Hilary,

    Now I got this message:

    Friday, October 13, 2017 7:17 AM
  • Try 2 then.
    Friday, October 13, 2017 9:49 AM
  • Hilary,

    Result is same:

    Friday, October 13, 2017 11:08 AM
  • You right Hilary about -LoginTimeout:)

    I would try increasing -QueryTimeout (I think it is in seconds)

    Friday, October 13, 2017 12:53 PM
  • Its hard to say where it is locking or hanging. I strongly suspect the cardinality estimator is at fault here. 

    Try setting trace flag 9481  to see if that solves it - do this for diagnostic purposes.

    Can you give us any details about the table/publication? How many rows, anything non plain vanilla?

    Friday, October 13, 2017 1:59 PM
  • Its hard to say where it is locking or hanging. I strongly suspect the cardinality estimator is at fault here. 

    Try setting trace flag 9481  to see if that solves it - do this for diagnostic purposes.

    Can you give us any details about the table/publication? How many rows, anything non plain vanilla?

    If I turn on trace flag 9481, I will force the query optimizer to use version 70 of the cardinality estimator when creating the query plan. But I want to have cardinality estimator for SQL 2016.

    Database subscriber have 131778,56 MB. Publication have 412 article (90% tables), Transactional Replication.

    Monday, October 23, 2017 10:16 AM
  • It appears that as changing the database compatibility level to a previous version solves the problem that the Cardinality Estimator is the problem. 

    As using the trace flag is not an option for you, you will need to get Microsoft involved to come up with an acceptable workaround or perhaps use the initialize from backup option.

    Monday, October 23, 2017 11:50 AM