"lock request time out period exceeded" Error When Trying to See DB Hierarchies

I got this problem when I began an explicit transaction in which I created a table in tempdb from a script running in another database (not tempdb). When I committed the transaction, the commit didn't seem to release the lock on the table I had created in tempdb.

Thanks to this page, I USEd tempdb and executed DBCC OPENTRAN and got the SPID of the connection to tempdb that was causing the lock. Then I KILL <SPID number> to kill it.

Not very graceful, and I lost all the information in the table I had created in tempdb, but that was OK in my case.

As many had already pointed out, usually there's a long lasting transaction, mostly caused by the miss used SET IMPLICIT TRANSACTIONS ON, which should not be used at all. To see why check Brent Ozar's insightful article

Anyway, you can get a list of long lasting pending transactions using the following query.

SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd], [s_est].text AS [Last T-SQL Text], [s_eqp].[query_plan] AS [Last Plan] FROM sys.dm_tran_database_transactions [s_tdt] JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id] JOIN sys.[dm_exec_sessions] [s_es] ON [s_es].[session_id] = [s_tst].[session_id] JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id] LEFT OUTER JOIN sys.dm_exec_requests [s_er] ON [s_er].[session_id] = [s_tst].[session_id] CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est] OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp] where [s_tdt].[database_transaction_begin_time] is not null ORDER BY [Begin Time] ASC; 

https://www.brentozar.com/archive/2018/02/set-implicit_transactions-one-hell-bad-idea/

Excluding Harware consideration, perhaps you need to run the script to check what are the activity withholding the SQL Session, one of the common scenario is not to use an Implicit transactions Option in SQL Server Management Studio.

In my case it was someone running a transaction with a BEGIN TRAN but no ROLLBACK or COMMIT.

The transaction was locking the system tables.

This the commands that helped me debug and solve the issue:

-- Find the open transactoin DBCC opentran() -- Check the details of the query and the query plan exec sp_who2 129 exec sp_lock 129 exec sp_WhoIsActive @get_plans = 1, @get_transaction_info = 1 -- Kill the process Kill 129 

"When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded."

I had exactly same issue. I went to the query execution window and; typed and executed ROLLBACK statement.

Looks like some of the series of statements I was executing prior to that, held open transaction. Specifically, because some of them where DDL statements. Once I issued rollback, the object hierarchies started to work.

There are so many things this could be that all I can offer are a few questions to help guide you towards an answer.

1. Is the DB on a server dedicated to just running SQL Server? If not, other processes might be interferring by stealing precious processor time.

2. Is the DB server essentially out of memory? SQL Server will attempt to allocate every single byte it can, but if it's at capacity and your queries require more data to be loaded then it has to fallback to using virtual memory, which radically increases the amount of time even simple queries might take.

3. Is the DB server's network bandwidth to small to handle transferring the data in a timely manner?

At the end of the day, it sounds like the machine you are hosting SQL Server on is under sized for what you are trying to do. It's entirely possible that you have finally reached those hardware limits where performance is dropping off radically. If this is the case (the above questions will help you determine that) then you'll want to move the DB to a server that is properly sized for the amount of data (and queries) you are trying to process.

This could mean using faster processors, faster drives, or just installing more RAM.

I've gotten the same error after running script to create database with transaction containing CREATE TABLE and CREATE PROCEDURE clauses for these tables like this:

create database NewDB; go use NewDB; begin tran CreateStructure create table dbo.... ... go create procedure ... commit tran CreateStructure 

After I moved create procedure statement out of transaction the error was gone.

I had this error pop up after a database shrink operation completed (but didn't release the space). To fix it:

1. CHECKPOINT (this took a bit to run)
2. Shrink the file again with "Release unused space".

I had a issue where after updatign the stored procedure the update was not getting saved and when try to access again i was getting this error msg that lock request time out exceded I simply uncheked the Set impilicit transaction and this worked for me