1

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 ?

enter image description here

0

I agree with David Browne - Microsoft that you can look at sys.dm_tran_locks and sys.dm_os_waiting_tasks.

You can also see which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to.

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.

1
  • 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. Jun 19 '20 at 17:25
1

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

Look at sys.dm_tran_locks.

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

2
  • 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. Jun 19 '20 at 17:25
  • The conflicting locks could have come from earlier in a transaction. Jun 19 '20 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.