I can not navigate through the objects in sql server managemente studio - lock never goes away

Question

Whenever I try to navigate through the objects in sql server management studio object explorer I get the error message

Lock request time out period exceeded. (Microsoft SQL Server, error: 1222).

It is impossible to list columns in tables, show code of views and procedures, etc.

It's a SQL Server 2016.

How to look for the cause of this ?

There is a lot of ETL running on the server, can that be the cause ? How to pinpoint the cause ?

Marcello Miorelli · Accepted Answer · 2020-06-19 06:56:38Z

0

But if you want to make your life easier have a look at sp_whoisactive or some alternatives also good.

You can also start monitoring blocking.

answered Jun 19 at 6:56

Marcello Miorelli

12.6k34 gold badges103 silver badges209 bronze badges

I found an update of a user table from a join of this table and another user table, using a user defined function to get only digits from a string. This update blocks a query that accesses sys.extended_properties and sys.tables. I do not see any reason for this block. – Jeferson Navarro Jun 19 at 17:25

add a comment |

David Browne - Microsoft · Accepted Answer · 2020-06-18 22:15:28Z

1

Someone's got an open transaction involving the system tables.

While SSMS is waiting you can examine sys.dm_os_waiting_tasks to see which session it's blocked by.

answered Jun 18 at 22:15

25.2k2 gold badges25 silver badges59 bronze badges

I found an update of a user table from a join of this table and another user table, using a user defined function to get only digits from a string. This update blocks a query that accesses sys.extended_properties and sys.tables. I do not see any reason for this block. – Jeferson Navarro Jun 19 at 17:25
The conflicting locks could have come from earlier in a transaction. – David Browne - Microsoft Jun 19 at 17:36

add a comment |

2 Answers 2