Share via

Transaction replication is getting failed for creating snapshot with message as "Prefetch objects failed for Database"

Ravi Padakanti 0 Reputation points
2024-07-12T11:33:24.7133333+00:00

This issue "Message: Prefetch objects failed for Database " has been happening everytime I run the snapshot after 96% completed I was getting this issue (I need to wait for 5 hours for this). What ever .bcp files got created in storage account got lost after that exception. I had increased QueryTimeOut period for Distributor Agent didn't worked out.
Below is the error message:
Error messages:

Source: Microsoft.SqlServer.Smo

Target Site: Void PrefetchObjectsImpl(System.Type, Microsoft.SqlServer.Management.Smo.ScriptingPreferences)

Message: Prefetch objects failed for Database 'xxx'.

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: Microsoft.Data.SqlClient.SqlDataReader GetExecuteReader(Microsoft.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, IEnumerable`1 extraFields)

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 11228DEV.1645BFFADFE7.DATABASE.WINDOWS.NET, Level 11, State 0, Procedure , Line 0

Execution 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

Source:

Target Site:

Message: The wait operation timed out

Stack: (Source: <Unknown>, Error number: 0)

Get help: http://help/0

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,327 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LucyChenMSFT-4874 2,985 Reputation points
    2024-07-15T01:52:17.46+00:00

    Hi @Ravi Padakanti ,

    Thank you for your reaching out and welcome to Microsoft Q&A!

    Here is a known issue like yours, you can follow the steps in this thread to troubleshoot the issue and try to resolve it!

    Feel free to share your issue here if you have any concerns.

    Best regards,

    Lucy Chen

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    https://docs.microsoft.com/en-us/answers/support/email-notifications

    0 comments