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