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