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 '20 at 6:56

Marcello Miorelli

14.1k39 gold badges119 silver badges241 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 '20 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 '20 at 22:15

35.6k3 gold badges38 silver badges79 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 '20 at 17:25
The conflicting locks could have come from earlier in a transaction.
– David Browne - Microsoft
Jun 19 '20 at 17:36

Add a comment |

2 Answers 2