TFS2008: upgrade from TFS 2005 to TFS 2008+SP1 fails with SQL Exception: Lock request time out period exceeded.

  • Article

While upgrading TFS 2005 to TFS2008 with SP1 slipstreamed (for instruction on Slipstreaming TFS SP1 to TFS 2008 installation media please download the latest TFS 2008 install guide from:https://www.microsoft.com/downloads/details.aspx?familyid=ff12844f-398c-4fe9-8b0d-9e84181d9923&displaylang=en), the upgrade might fail with an error

Error 29002.The Team Foundation databases could not be repaired. For more information, see the Microsoft Windows Installer (MSI) log.

MSI Log Snippet:

at Microsoft.TeamFoundation.DatabaseInstaller.CommandLine.CommandLine.RunCommand(String args)
> Inner Exception:
SQL Error #1
SQL
Lock request time out period exceeded.
SQL LineNumber: 1
SQL Source: .Net SqlClient Data Provider
SQL Procedure:
SQL Error #2
SQL Msg: ALTER DATABASE statement failed.
SQL LineNumber: 1
SQL Source: .Net SqlClient Data Provider
SQL Procedure:
System.Data.SqlClient.SqlException: Lock request time out period exceeded.
ALTER DATABASE statement failed.
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.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.TeamFoundation.DatabaseInstaller.DataDeployer.ExecuteSql(SqlConnection sqlConnection, String batch)
at Microsoft.TeamFoundation.DatabaseInstaller.DataDeployer.DeployScript(String script)
Exception Data:
Key: HelpLink.ProdName, Value: Microsoft SQL Server
Key: HelpLink.ProdVer, Value: 09.00.3073
Key: HelpLink.EvtSrc, Value: MSSQLServer
Key: HelpLink.EvtID, Value: 1222
Key: HelpLink.BaseHelpUrl, Value: https://go.microsoft.com/fwlink
Key: HelpLink.LinkId, Value: 20476
Line 28006
Error: Lock request time out period exceeded.
ALTER DATABASE statement failed.
02/12/09 21:09:47 DDSet_Status: Process returned 100
02/12/09 21:09:47 DDSet_Status: Using default error code for return value '100' and it is: '29002'
02/12/09 21:09:47 DDSet_Error: 100
MSI (s) (9C!B4) [21:10:00:432]: Product: Microsoft Visual Studio 2008 Team Foundation Server - ENU -- Error 29002.The Team Foundation databases could not be repaired. For more information, see the Microsoft Windows Installer (MSI) log.

Cause:

The SQL exception: "Lock request time out period exceeded" occurs when you change the “user access” attribute of a database from “restricted-user mode” to “multiple-user mode” in SQL Server 2005 with SP2.

Resolution:

To fix the issue, install the Cumulative update package 7 for SQL Server 2005 Service Pack 2 (https://support.microsoft.com/kb/949095/LN/ )

Note:

When you experience this issue during the upgrade, the error message gives you an option to "retry" or "cancel". Cancelling the upgrade doesn't rollback the changes; hence the databases will be in partially upgraded mode. Install the Cumulative update package 7 for SQL Server 2005 Service Pack 2 and restart the TFS upgrade again, the upgrade should complete successfully.

Content by: Arunrama

Reviewed by: Lucky