Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Snapshot Replication Error


Snapshot Replication Error

Author
Message
asm1212
asm1212
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 432
Hey guys,

So I have setup replication between our source production database to replicate to our reporting database so we are not hitting our production database for reporting...

I set this up 2 months ago and everything was running fine...Until about 5 days ago, it started failing every night, when I go to look at the job history and see the big red X and look at the error, it gives me this:

Message
Executed as user: TRH\mssql. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). The step failed.


For some reason I was under the impression it was failing during the distribution step of replication...I know what that error means but I started digging...This was yesterday - I created a backup of the source, and restored it onto reporting...Was going to see if that helped the issue...I check this morning and nope same failure, same message...So I have this redgate tool SQL Compare...It compares the schema's of the two databases! Once it finished, it showed no differences and 22723 identical objects!

When looking back at the error, the light bulb came on and was like this is failing on the snapshot portion of replication! Now it makes no sense to me why I would get that error when trying to create a snapshot??? Its not pushing data anywhere so I dont know why this error would be thrown...

Looking at the job history and expanding down on the today's date with big red X, I noticed some of the lines had a blue curved arrow and some of the lines have a the successful green play button, but looking at the blue curved arrow line, I noticed this:

2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.
2013-07-23 07:17:43.55 Source: Replication
2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException
2013-07-23 07:17:43.55 Exception Message: Failed to read column data
2013-07-23 07:17:43.55 Message Code: 0
2013-07-23 07:17:43.55
2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.
2013-07-23 07:17:43.55 Source: Replication
2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException
2013-07-23 07:17:43.55 Exception Message: Failed to read column data
2013-07-23 07:17:43.55 Message Code: 0
2013-07-23 07:17:43.55

Now let me say this again, there was not a red X on this line, just the blue curved arrow! and the line above it is a successful green one...Then it 2 lines above it is when I see the red X errors with the String or Binary data would be truncated...


Details of the Snapshot Replication:
Source to Reporting
A bunch of articles - like at least 10,000
Security Settings - Run under the SQL Server Agent service account , Connect to the publisher by impersonating the process account
Runs every morning around 3 am

Can anyone give me any insight as to what might be going on here?
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
Could there be a sporadic connection problem? That's what the error message seems to suggest.

Also, try checking the logs of the agent jobs on the publisher, distributor and subscriber. And look at the MSrepl_errors and MSrepl_commands tables and the sp_browsereplcmds procedure in the distribution database to track down more information.

Sorry not to be more help, but replication issues can be extremely tricky to track down. I've had different issues myself but found the above tables and logs to be much more useful than replication monitor in getting to the root of the problem.

Duncan
asm1212
asm1212
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 432
Thank you...

I looked in the distribution.dbo.MSrepl_errors table...

The first error is this:
Message: Failed to read column data Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem) at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper()


Then directly after it is this error:
Message: TCP Provider: An existing connection was forcibly closed by the remote host. Stack:


And then both of these error msgs go on for the next 20 min...And the final errors I get are:
Message: Communication link failure Stack:

Message: TCP Provider: The specified network name is no longer available. Stack:

Message: TCP Provider: An existing connection was forcibly closed by the remote host. Stack:

Message: Communication link failure Stack:


Any idea what this means?
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
I really don't know what it means, sorry. The only thing I can find that i similar is this post, which is for SQL 2008, and doesn't really offer a solution except to suggest it's a bug in SQL Server. Are you sure there's no possibility of connection problems to the snapshot folder, either by the publisher or subscriber?

You can trace the error further though, using the MSrepl_commands table and sp_browsereplcmds procedure to find out what the command is doing that generates the error. Use the xact_seqno and command_id to get the record from MSrepl_commands then use the publisher_database_id, xact_seqno and article_id values from there to feed to the sp_browsereplcmds procedure. If nothing else, you'll get to learn more about the underlying workings of replication Hehe. But you may get something else to google*

Duncan

*(other search providers are available)
Joe Zonum
Joe Zonum
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 438
Did you ever find a solution to this issue?
asm1212
asm1212
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 432
No I did not...We ended up ditching the replication process and created a backup/restore job!
Joe Zonum
Joe Zonum
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 438
ty for the reply
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search