Transactional Replication | Snapshot Agent | Error: "Failed to read BLOB column" - by SQL Ranger

Status : 

  External<br /><br />
		This item may be valid but belongs to an external system out of the direct control of this product team.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 699950 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/8/2011 9:52:50 PM
Access Restriction Public

Description

Transactional Replication has been configured between 2 SQL Server 2008 R2 instances.  There is a single Publication with 60 articles. Initial snapshot worked. (Around 120GB of BCP files were generated.)

OLTP_SQLServer2008R2 (10.50.1777) is a PUBlisher. OLAP_SQLServer2008R2 9(10.50.2789) is the DIStributor and SUBscriber.

Replication is working fine.

Business now wants to add 5 new articles to Publication. (4 tables contain a couple of 100 records, one table contains 1,600,000 rows.)

Publication is change to reflect the 5 new tables.

Snapshot Agent is done after business hours. (Replications starts generating snapshot for all 60+ tables, not just the 5 new ones. Which is not ideal, so that might need to be tweaked, as I believe it should just snapshot the new articles in the publication, no?)

In any case the Snapshot Agent does not complete. Errors indicate "Failed to read BLOB column". Snapshot Agent retries a number of times. Same error. Finally it stops retrying after a number of times.

Replication is no longer working. Replication Monitor shows that there are 234,666 commands waiting in the distribution database which will take over 1 hour to complete.
Sign in to post a comment.
Posted by Microsoft on 5/15/2012 at 8:51 AM
Hi SQL Ranger ,

given my previous message, I'm archiving this work item for now.

Best regards
Jean-Yves Devant

Program Manager (Replication/CDC/CT
Posted by Microsoft on 4/30/2012 at 9:04 AM
Hi SQL Ranger,
    

sorry for the delay on this. To me what you described is related to 2 different things:
1- BLOB related issues.
2- How the snapshot agent is suposed to be working when you add articles.

As I mentionned, for the BLOB errors and the other behavior you have observed I recommend you work with our support engineers if the issues still occur. What you described requires some troubleshooting/investigation to pin point a specific issue on which we can comment/help. Our support engineers do have means to engage with Product Group directly in case there is a customer issue they cannot sort out or if they need a confirmation if a specific pin-pointed behavior is expected or not.

From the snapshot agent perspective, yes it is expected that the entire snapshot is being regenerated when you run it after you have added articles but when you deliver it to an existing subscriber then only the schema and the data for the newly added articles are being sent.


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
Posted by SQL Ranger on 11/15/2011 at 9:16 PM
Hi,

By way of feedback, I suspect/think (but cannot prove) that your advice might be incorrect.

What I mean by that is that I suspect that these errors were caused tables being locked in the publisher database. The BCP operations timed out and generated theses generic errors.

My reasoning is that when I peformed the snapshot after hours when eveyone went home and I killed off some processes that were connected to the system it worked fine. (I did this on Monday night).

So on that night Replication generated the 65 articles' schem and BCP files without a problem. (1 article is a VIEW.)

WEIRD THING IS THAT ONLY 37 TABLES OF THE 64 TABLES WERE REPLICATED. There were no errors anywhere that I could see in the GUI tools. Everything seemed delivered.

So on the next night (Tuesday) I completely uninstalled replication and started from scratch. This time round when it came to the snapshot it failed on the first 3 attempts but succeeded on the 4th. So it generated all the 64 tables and the view and populated them with data.

WEIRD THING NOW IS THAT THE "Undistributed Commands" KEEPS GROWING ALTHOUGH EVERYTHING SEEMS TO BE FINE. I am going to submit another Connect Item on that.
Posted by SQL Ranger on 11/10/2011 at 5:02 PM
Hi Jean-Yves,

Can you elaborate whether we have one problem or two?
1. The BLOB error (Hopefully solved in the upcoming CU and related to http://support.microsoft.com/kb/979549)
2. Replication wanting to generate a snapshot for all tables. I was told by a replication expert that it should only perform a snapshot of the new articles. (In our case 6 new tables)

As I still might have your ear, is there any resource out there that you are aware of that goes more into the internals of how the Replication technolocgy (what happens in th e GUI which calls various PROCs). Because that impact when we run things in production. BOL can be a bit sparse at times...

TIA!
Posted by Microsoft on 11/10/2011 at 9:15 AM
Hi SQL Ranger


I looked at the stack you shared.It is very similar to an issue for which we have delivered a fix in SQL 2008 R2 RTM Cumulative Update #10 (build 10.50.1807) that shipped on October 17th 2011. This will be ported to SQL 2008 R2 Service Pack 1 Cumulative Update #4 that will ship during the second half of December.

Could you please give it a try and report back whether or not you still have the issue.

Thanks
Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Posted by Microsoft on 11/10/2011 at 9:03 AM
Hi SQL Ranger,
    
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.
If this is currently a production down situation I recommend you work with our support engineers to move forward on this issue. You may already have a local support representative. If not you'll find more information on http://www.microsoft.com/microsoftservices/en/us/support.aspx.


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team