Team Foundation Server (TFS) Replication Error for TFS Attachments

Problem:

Team Foundation Server (TFS) failure attempting to add
an attachment when replication is configured on SQL backend version 2008 R2 (RTM) - 10.50.1617.0 (X64) Enterprise
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1).

Error:

TF237082: The attachment could not be uploaded. Verify that
you have a network connection and that Team Foundation Server is available.

HTTP Status
Code: InternalServerError – TFS51334: An unknown Web service error occurred:
UPDATETEXT is not allowed because the column is being processed by a concurrent
snapshot or is being replicated to a non-SQL Server Subscriber or Published in
a publication allowing Data Transformation Services (DTS) or tracked by Change
Data Capture.

Solution:

Step 1 ~ Root cause could be size restriction on the publisher. Verify that SQL replication
is configured to handle the maximum text size.

exec sp_configure 'max text repl size', '2147483647'

reconfigure with
override

 

Step 2 ~ Root cause could be incorrect or lingering old metadata
related to the publication. In our case there was a column in the TFS database
having a value > 0 in its  is_non_sql_subscribed
column.

 

select @@SERVERNAME

go

use Tfs_Production

go

select object_id,name,
is_dts_replicated, is_column_set, is_merge_published,
is_non_sql_subscribed, is_replicated, *

from sys.columns

where object_id
= object_id('Attachments')

go

select id,
name, colstat

from sys.syscolumns

where id =
object_id('Attachments')

go

 The quickest way we could reset the column and resolve the problem was to do the following:

1)   Drop all replication components (subscriptions, publications, and distribution database)

2)    Setup a small dummy database having 1 small table with a primary key

3)   Recreate distribution and publish the dummy table

4)   Create subscription for dummy publication

5)   Create publication on TFS database

6)   Create TFS subscription

7)   Drop dummy subscription, publication, and database