Timeout on TFS Warehouse building job

Have you ever experienced the following exception?

“Microsoft.TeamFoundation.Warehouse.WarehouseException: TF221122: An error occurred running job Work Item Tracking Warehouse Sync for team project collection or Team Foundation server <TFS_COLLECTION>. —> Microsoft.TeamFoundation.WorkItemTracking.Server.SqlCommandTimeOutException: Timeout expired”.

I’ve done these days and it was incredible hard to find out a solution: it looked like the job was stopped because every time I run this query, the result didn’t change.

SELECT COUNT(*)FROM[Tfs_Warehouse].[dbo].[DimWorkItem]

Going deeper into the issue, I’ve discovered that everyday the warehouse building job was failing because of a SQL timeout. I’ve tried to rebuild the warehouse, stop job and run it manually, disable all the job and run only that one but nothing helped.

Finally, I’ve found the solution, manually creating two indexes on the TFS collection database (please, don’t ask me why they weren’t already in TFS database):

CREATE NONCLUSTEREDINDEX[IX_WorkItemsWere_ID_Rev]

ON [dbo].[WorkItemsWere]([PartitionId],[ID],[Rev])

INCLUDE ([Revised Date],[Changed Date],[AreaID],[State],[Authorized Date])

GO

CREATE NONCLUSTEREDINDEX[IX_WorkItemsLatest_ID_Rev]

ON [dbo].[WorkItemsLatest]([PartitionId],[ID],[Rev])

INCLUDE ([Revised Date],[Changed Date],[AreaID],[State],[Authorized Date])

GO

After running it, the warehouse started immediately to be populated again! Nice!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s