Blocking problems may occur when you use SQL Server Enterprise Manager to make design changes to a table (839417)

SYMPTOMS

When you try to modify the design of a Microsoft SQL Server 2000 table by using SQL Server Enterprise Manager, blocking problems that involve the table may occur in your instance of SQL Server. These problems may occur when you perform one of the following design changes to the SQL Server table, and then save the design changes by using SQL Server Enterprise Manager:

You add a new column to your table.
You delete an existing column from your table.
You modify the properties of an existing column in your table.
You change the seed value for an existing IDENTITY column in your table.

WORKAROUND

To work around this problem, perform the design changes to your SQL Server table by running the appropriate Transact-SQL statements in SQL Query Analyzer:

If you want to add a new column to your table or to delete an existing column from your table, run the ALTER TABLE Transact-SQL statement in SQL Query Analyzer. For more information about the ALTER TABLE Transact-SQL statement, see the "ALTER TABLE" topic in SQL Server Books Online.
If you want to modify the seed value of an existing IDENTITY column in your table, run the DBCC CHECKIDENT Transact-SQL statement in SQL Query Analyzer. For more information about the DBCC CHECKIDENT Transact-SQL statement, see the "DBCC CHECKIDENT" topic in SQL Server Books Online.

MORE INFORMATION

In SQL Server 2000, when one client connection holds a lock on a particular resource, such as a table, and a second client connection tries to acquire a conflicting lock type on the same resource, the blocking problem occurs. The time that the first client locks the resource is generally very short. Therefore, as soon as the first connection releases the lock, the second connection acquires the required lock type on the resource. However, when you make the design changes by using SQL Server Enterprise Manager, and you try to save the design changes, the table is locked for a long time. Therefore, all the other client connections that are using the same table may be blocked.

REFERENCES

For additional information about blocking problems, click the following article numbers to view the articles in the Microsoft Knowledge Base:

224453 INF: Understanding and resolving SQL Server 7.0 or 2000 blocking problems

323630 INF: Resolving blocking problems that are caused by lock escalation in SQL Server