Microsoft KB Archive/934005

Knowledge Base

How to configure SQL Server 2005 to avoid automatic escalation to table locks after a large user operation occurs against Visual Studio 2005 Team Foundation Server

Article ID: 934005

Article Last Modified on 4/30/2007

---

APPLIES TO

• Microsoft Visual Studio 2005 Team Foundation Server

---

INTRODUCTION

A large user operation against Microsoft Visual Studio 2005 Team Foundation Server may cause the Microsoft SQL Server 2005 Database Engine to escalate to a table lock. Typically, this behavior occurs during large source code control operations.

For example, a large source-code control operation involves checking in approximately 5,000 files. As soon as an operation acquires 5,000 individual locks, the Database Engine automatically escalates the locks to a table lock. This table lock blocks all access to the table. This blocked access includes unrelated table access. For example, simple read operations and simple get operations cannot access the table.

This article describes how to configure SQL Server 2005 to avoid automatic escalation to table locks after an operation acquires 5,000 individual locks.

MORE INFORMATION

If you experience the issue that is described in the "Introduction" section, you can configure SQL Server not to escalate individual locks into a table lock.

To do this, follow these steps.

Note You may experience an out-of-lock memory exception when you disable lock escalation for large operations on a 32-bit computer.

1. Disable lock escalation. To do this, follow these steps
   1. Click Start, click Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
   2. Click SQL Server 2005 Services, right-click SQL Server (InstanceName), and then click Properties.

   3. Click the Advanced tab, and then add the following line of code to the end of the Startup Parameters string:

      ;-T1211

   4. Click OK to close the SQL Server (InstanceName) Properties dialog box.
   5. Click SQL Server 2005 Services, right-click SQL Server (InstanceName), and then click Restart.
2. Disable row locking on the tbl_PendingChange table and the tbl_LocalVersion table. To do this, follow these steps:
   1. Click Start, click Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
   2. Expand Database, right-click TfsVersionControl, and then click New Query.

   3. Add the following code example to the new query windows.

      EXEC sp_indexoption N'tbl_LocalVersion.PK_tbl_LocalVersion', 
                          N'DisAllowRowLocks',
                          TRUE;
      
      EXEC sp_indexoption N'tbl_LocalVersion.IX_tbl_LocalVersion_WorkspaceId_LocalItem', 
                          N'DisAllowRowLocks',
                          TRUE;
      
      EXEC sp_indexoption N'tbl_PendingChange.PK_tbl_PendingChange', 
                          N'DisAllowRowLocks',
                          TRUE;
      
      EXEC sp_indexoption N'tbl_PendingChange.IX_tbl_PendingChange_TargetServerItem', 
                          N'DisAllowRowLocks',
                          TRUE;
      
      EXEC sp_indexoption N'tbl_PendingChange.IX_tbl_PendingChange_ItemId_WorkspaceId', 
                          N'DisAllowRowLocks',
                          TRUE;

   4. Click Execute.

Additional query words: TFS SQL2005 vs2005

Keywords: kbcode kbinfo kbhowto KB934005

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.