Today, we got a service request that our customer got the following error message: Lock request time out period exceeded. (.Net SqlClient Data Provider) using SQL Server Management Studio. Following I would like to share with you some details about this issue and how to find and fix it.
The first thing is to run a transaction without adding commit transaction, for example:
begin transaction
CREATE TABLE IX7 (ID INT)
Second, modify the SET LOCK TIMEOUT in SQL Server Management Studio->Tools->Options->Query Execution->SQL Server->Advanced change the SET LOCK TIMEOUT from -1 to 10000 miliseconds.
Third, go to the Object Explorer and try to expand tables and after around 10 seconds you are going to have the error message:
Why is happening this error?
First, let's investigate what is doing SQL Server Management Studio to fill up the list of the tables, using SQL Server Profiler of Azure Data Studio.
- Following the instrucctions, SSMS specified SET LOCK TIMEOUT 10000 and after it try to execute the query: SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], tbl.name AS [Name], tbl.object_id AS [ID] FROM sys.tables AS tbl ORDER BY [Schema] ASC,[Name] ASC
- As sys.tables is now blocking by a previous CREATE TABLE instrucction we have to wait to release this blocking. Following our article here: Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub you could find the blocking issue:
- Finally, after this 10 seconds we are going to receive this error message meanwhile we don't execute the commit transaction of the execution of CREATE TABLE.
Sometimes, this issue could happen to performance issues.
Enjoy!