Have you ever encountered an error message when trying to view the properties of a database in SQL Server Management Studio (SSMS)? If so, you’re not alone. In this article, we will explore a common issue related to locking and timeout in SQL Server.

Recently, while attempting to view the properties of the tempdb database in SSMS, I encountered an error message stating “Lock request timeout period exceeded.” Initially, I assumed that the database had a size problem and extended the file size of tempdb. However, even after doing so, I was still unable to view the properties of tempdb. Upon further investigation, I confirmed that there was enough free space in tempdb and no error messages in the SQL Server error log. Everything seemed to be in order.

To dig deeper into the issue, I decided to run a profiler trace and monitor the process when clicking the “Properties” menu. Surprisingly, there were no error messages related to tempdb. This led me to believe that the problem might be on the client side rather than with SQL Server itself. To test this theory, I enabled the “User Error Message” option, and this time I received the error message “Lock request timeout period exceeded.” The script shown in the error message indicated that it was being blocked by another session, resulting in the timeout and the subsequent error message.

My next step was to identify the blocking script. By reproducing the error message and simultaneously checking the blocking script, I discovered that session 79 was the culprit. The wait resource was identified as key 2:327680. To determine the table and index associated with this key, I used the following script:

SELECT OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE hobt_id = 2 AND partition_id = 327680;

Upon executing the script, I found that session 79 had an exclusive lock (xlock) on the sys.sysrowsets table. This raised the question of why SSMS was querying the sysrowsets table when clicking the “Properties” menu. Further investigation revealed that the timeout query was retrieving data from the sys.partitions table, which is a view based on sys.sysrowsets.

It turned out that session 79 was running a large transaction involving temporary tables and complex queries. Unfortunately, I was unable to modify the code of the application responsible for this transaction. As a result, the only option was to wait for the transaction to finish. However, at least I knew that the tempdb was functioning properly and SQL Server was running smoothly. Therefore, I could safely ignore this error.

In conclusion, encountering locking and timeout issues in SQL Server is not uncommon. It is important to understand the underlying causes and take appropriate actions to resolve them. In this case, identifying the blocking script and understanding the query being executed shed light on the issue. Although I couldn’t directly resolve the problem, knowing that the database and SQL Server were functioning correctly provided reassurance.