TF53010 - WAREHOUSE update error - Urgent

Forgot to say we're running TFS SP1

Log around the error is;

[DW] [Error, PID 6128, TID 8264, 17:09:09.114] TF51209: A run-time error System.Data.SqlClient.SqlException: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.TeamFoundation.Warehouse.AdapterDataStore.SetAreaPath()
at Microsoft.TeamFoundation.Warehouse.CommonStructureAdapter.UpdateCssDimension()
at Microsoft.TeamFoundation.Warehouse.CommonStructureAdapter.MakeDataChanges()
at Microsoft.TeamFoundation.Warehouse.AdapterWrapper.RunTimerAdapter() occurred on adapter Microsoft.TeamFoundation.Warehouse.CommonStructureAdapter.

[DW] [Info, PID 6128, TID 8264, 17:09:09.114] Adapter Microsoft.TeamFoundation.Warehouse.CommonStructureAdapter exited.

DavidF at 2007-9-6 >

Hi David,

How many rows do you have in your Area table? Do you notice any suspicious looking records?

Would you try to run the following sproc against your data and let me know if you still run into the max recursion issue?

Thx,

J

ALTER PROCEDURE [dbo].[prc_Set_Area_Path]

AS

SET NOCOUNT ON

SET XACT_ABORT ON;

BEGIN

WITH TmpArea([Area], [__ID], [__Parent_ID], [Path]) AS

(

SELECT

Anchor.[Area]

,Anchor.[__ID]

,Anchor.[__Parent_ID]

,CAST(('\' + Anchor.[Area]) AS nvarchar(4000))

FROM [dbo].[Area] AS Anchor

WHERE [__Parent_ID] = [__ID]

UNION ALL

SELECT a1.[Area]

,a1.[__ID]

,a1.[__Parent_ID]

,CAST((a2.[Path]+'\'+ a1.Area) AS nvarchar(4000))

FROM [dbo].[Area] AS a1 JOIN TmpArea a2

ON a1.[__Parent_ID] = a2.[__ID] AND a1.[__Parent_ID] <> a1.[__ID]

)

Update [dbo].[Area]

SET [dbo].[Area].[Area Path] = ta.[Path]

FROM TmpArea AS ta

WHERE [dbo].[Area].[__ID] = ta.[__ID]

IF (@@ROWCOUNT > 0)

BEGIN

EXEC [dbo].[prc_WarehouseUpdate_Update] 'Area', 2

END

END

JimmyLi-MSFT at 2007-9-6 >

Hi Mauli

Sorry for the late reply, release dealines etc.

We had to raise an support incidemt with MS in the end, they recommended a fix very similar to Jimmy's.

Looks like corrupted data was the cause, but how that data entered the system (could only have been VS) was never established.

The only thing we could link it to was one of our team leads was setting up Areas and Iterations for one of our larger projects, problems started shortly after.

Thanks all

David

DavidF at 2007-9-6 >