Check Open Transaction

Using DBCC OPENTRAN

Here, I show how DBCC OPENTRAN can also be used to quickly narrow down the specific problem below.

When working with SSMS, have you run into a situation where you try to expand the list of tables and SQL Server came back with this nice message?

Lock request time out period exceeded. (.Net SqlClient Data Provider)

This is because someone is altering a table in the database you are working with and that session has reserved a schema-modification lock.

Also, you could find out what operation is causing blocking and who is behind the associated SQL so that you can talk to that person to find out when that will complete (or) to your DBA to kill some run-away DDL.

Check for open transactions

DBCC OPENTRAN

If there is open transaction, it should return results. Use KILL xx to kill the open transaction.

If there is a open transaction, it will lock up certain tables and prevent certain queries from completing.

You can also use the WITH (NOLOCK) option to query while there is a lock on certain tables.

Sources:

SQL Server: SSMS Says “Lock request time out period exceeded.” – Identify Blocking Source Quickly