[Troubleshoot] Run Profiler to find the maximum number of locks in the system when you view the current activity in the Enterprise Manager.

Some detours were taken to solve the problem, but some things in the process may be a bit of authentication in the future, so they were recorded.
 
The customer reported that the system had been unable to perform a storage operation before 10 a.m. for a few days, and it would be OK after 10 a.m.
The customer's environment is. for Web applications developed by. Net, the SQL Server 2000 database is used, and the data volume in the table is hundreds of thousands or millions, as a result, we initially suspected that some operations had locked some tables before (our system does have some operations, and a factory needs to run for 1, 2, 10, and half an hour, is equivalent to the enterprise's MRP calculation), so the related storage operations are blocked. Starting from this point, it is a waste of time to Review the SQL statements of some suspected tables, but no problems are found.
Later, when a problem occurs, log in to the customer's system environment and check that not only is it saved before, queries involving this object cannot be performed (the customer did not find this and therefore did not notify me ). Even if the update/delete table is locked, it cannot be queried. Therefore, it is estimated that the index is occupied by an exclusive lock and not released. Starting from this point, I soon focused on a database index reorganization Job running at every morning. However, I couldn't find any clue about the program logic of the Job, because the program logic will not cause problems.
Then, wait for another day and remotely connect to the customer's database server in case of a problem for troubleshooting. Dbcc checktable and dbcc checkdb are all correct. dbcc showcontig shows that the index status is good. Check the Current Activity on the Enterprise Manager and find that an error cannot be opened. Two messages are found before and after the error message, which roughly means a timeout error and one means that the system has reached the maximum number of locks. This error is confusing because there were very few users using the system before, and the CPU usage of the database server was quite low. In this case, the maximum number of locks would not be reached. If you cannot see the process in the Enterprise Manager, you can only directly check sysprocesses and syslockinfo, but there are hundreds of processes, because they are all object IDs and Page numbers, and they do not look intuitive, after a rough review, no records were found in syslockinfo using the table ID suspected to be locked. From the index reorganization Job, find a statement to organize the suspected table indexes. The execution of the statement is blocked and cannot end. Because this statement is blocked, the corresponding process is quickly found in sysprocesses and found to be blocked by another process, it is found that a series of processes are blocked by that process. Check that the process was started several days ago and has been hung. It adds a schema-type exclusive lock to a page. This process is performed by the customer on a machine using the Enterprise Manager or query analyzer to operate the database. If an exception occurs, it is estimated that the Enterprise Manager or query analyzer has been forcibly terminated. I have encountered such a situation before, and even if the machine is restarted after the enterprise manager or the query analyzer is forcibly terminated, the SQL Server process immediately calls out the task and continues to execute it (I do not know whether the SQL Server is continuing to execute or rolling back, or if it starts again, it will continue to be there ).
The problem was found: the page that the process has been occupying is where the index data of the suspected table is stored, and the index reorganization Job that started at AM to sort the index of the table is blocked, because of the schema-type exclusive lock, all data in this table cannot be accessed. Because the time-out time for the index reorganization Job was set to three hours, the index reorganization process exited at around due to a timeout error, so the table can be operated later.
Now, the solution is to end the process, but the problem is that the Enterprise Manager cannot enter the Current Activity to view the process, in this case, you cannot end the process through the Enterprise Manager. In the past, you never killed the process by using commands. You do not know which command is used. Therefore, use another machine to open SQL Profiler and find a process in the Enterprise Manager to end it. Check what is executed in SQL Profiler. It was found that it was a statement like kill 85. OK, kill the process on the server, solve the problem, and check the Current Activity in the Enterprise Manager. There is no error.
 
SQL Profiler is a good thing. The functions you can perform in the Enterprise Manager can basically monitor equivalent SQL statements through SQL Profiler.
For example, I used to be unfamiliar with system tables. To write some SQL Server management tools and ORM tools for SQL Server bidirectional interaction, I do not know how to access some table attributes, for example, the column identity, description attribute, Index Order, Clustered, and Unique attribute. You only need to open Profiler, perform operations in the Enterprise Manager, and analyze the SQL statements in Profiler to know the system tables and fields.
For example, you can view Locks/objects in the Current Activity of the Enterprise Manager using the following stored procedures. Use sp_helptext to view the SQL code of the stored procedure.
Sp_MSget_current_activity 52, 4, @ spid = 52