Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 ?
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.
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.
