2

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

1

3 Answers 3

1

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.

0
2

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.

1
  • The conflicting locks could have come from earlier in a transaction. Commented Jun 19, 2020 at 17:36
-1

You can use DBeaver instead of SSMS. It doesn't suffer from this issue.

Our organisation has very large legacy apps on SQL Server, but is gradually moving to PostgreSQL on AWS. As a result, I use both SSMS and DBeaver heavily every day. SSMS is great for SQL Server while DBeaver is great for PostgreSQL and passable for SQL Server.

When I encounter the issue described in the question in SSMS, I find DBeaver doesn't get blocked. While there may be a short delay, DBeaver is able to display all objects even when SQL Server is not able to display anything at all. I do not know enough yet about the DBeaver internals to know if it is achieving this by caching.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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