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.

| improve this answer | |
  • 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
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.

| improve this answer | |
  • 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

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.