29

I'm having problems with a database.

  1. I can run basic queries, albeit much slower than normal.

  2. 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.

  3. My SSRS reports that run on objects in this database are no longer completing.

  4. Jobs associated with procedures stored on this database also do not run.

I tried using sp_who2 to find and kill all connections on the database, however this has not solved the problem.

What is going on here? How can I resolve this?

3
  • Also see: stackoverflow.com/questions/12167570/…; not sure if that counts as a duplicate or not. Aug 28, 2012 at 21:17
  • Based on your comment to my answer below, I think you need to provide a lot more information. How is the server sized, have you watched it's performance counters, is it swapping to disk or otherwise resource starved in some way. Be sure to actually check the above and not just assume anything. Further, does this happen when you connect while remoted into the desktop? Is the problem only occuring when accessing from a single location? What is the network weather like for that server (and your connection to it)?
    – NotMe
    Aug 28, 2012 at 21:36
  • 3
    Sounds like you have open transactions that are blocking the read access to the tables. Aug 28, 2012 at 22:04

8 Answers 8

21

It was being caused by a perpetual rollback of a transaction. Had to eventually restart my server cluster

5
  • 2
    Restarting the service solved it for me. Dec 13, 2016 at 0:36
  • 1
    Restarting in such situation can lead you to Database Recovery
    – MaazKhan47
    Nov 7, 2018 at 5:55
  • dbcc opentran will tell you if there are open transactions Dec 17, 2018 at 16:21
  • I find it odd that while a transaction is running, I cannot expand the tables section for example. No data read, no DDL, nothing, just the list of tables.
    – gerleim
    Jun 26, 2019 at 13:41
  • What if we are using Azure Sql Server
    – user960567
    Sep 28, 2021 at 13:07
7

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.

5
  • Hi turbot, can you go into more detail about what you are suggesting?
    – Lloyd Banks
    Aug 28, 2012 at 21:45
  • It looks that while this isnt fully explained it might be a better answer, perpetual rollback of transactions that dont roll back, and are only enabled due to implicit transactions. Jul 31, 2013 at 17:21
  • 1
    looking back the question I couldn't say it must be perpetual rollback of a transaction. Judging the locking request time out period exceed i would say running implicit transaction option would give better clue of the causes.
    – Turbot
    Aug 1, 2013 at 3:18
  • Tools / Options / Query Execution / SQL Server / ANSI / SET IMPLICIT TRANSACTIONS
    – Tadej
    May 21, 2018 at 6:48
  • This fixed my issue, thanks
    – clint
    Sep 3, 2020 at 17:03
7

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.

1
  • In our case, a DML command (view redefinition) was issued againt database using SET IMPLICIT TRANSACTIONS ON without COMMIT TRANSACTION, which accidently caused a long lasting transaction. Using DBCC OPENTRAN helped quickly trace this issue. Jun 19, 2019 at 14:02
4

As many had already pointed-out, usually there's a long lasting transaction, mostly caused my the miss used SET IMPLICIT TRANSACTIONS ON, which should not be used at all. To see why check Brent Ozar's insightfull 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/

2

"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.

1

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.

2
  • It's not a hardware issue. The server cluster hosts multiple databases. This is the only database having problems
    – Lloyd Banks
    Aug 28, 2012 at 21:26
  • @LloydBanks: That doesn't mean this isn't a hardware problem. If I have 2 databases, one that's 20GB in size with a high transaction rate and another that's 1GB with a lower transaction rate then I'd expect the 1GB db to be swapped to virtual memory; which would increase query times. If the 20GB db was being hit hard enough, this could lead to connectivity issues with the smaller one.
    – NotMe
    Aug 28, 2012 at 21:34
1

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
-1

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".

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.