July 23, 2013 at 8:46 am
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?
July 25, 2013 at 6:31 am
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
July 25, 2013 at 9:14 am
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?
July 25, 2013 at 12:37 pm
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)
June 24, 2014 at 8:31 am
Did you ever find a solution to this issue?
June 24, 2014 at 11:10 am
No I did not...We ended up ditching the replication process and created a backup/restore job!
June 24, 2014 at 1:13 pm
ty for the reply
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply