Odd blocking condition - a SELECT query blocking on tempdb sysschobjs even though session sleeping

Question

Odd blocking condition - a SELECT query blocking on tempdb sysschobjs even though session sleeping

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
October 26, 2011 at 8:36 am

#250720

I had the following blocking condition this morning where someone was running a SELECT query from Management Studio on some tables in a user database;
that session was in SLEEPING state for several hrs after having completed in less than a sec.
During all that time it was blocking a SQL job on tempdb sysschobjs.
The blocked statement was this:
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE [name] like '%#tmp_BlockedERR%')
I'm puzzled at how this blocking could have occurred.
I was only able to fix the issue after killing the user's session.
Is this somehow related to the client user's intellisense turned on on SSMS?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Open transaction, no commit. Tran stays open so locks held even though the session was sleeping.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

GilaMonster (10/26/2011)
Open transaction, no commit. Tran stays open so locks held even though the session was sleeping.

Thanks, but why on tempdb? The SELECT was running on a user database.

I don't have evidence that the statement was running as part of an explicit transaction, but I will doublecheck with the user.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Marios Philippopoulos (10/26/2011)
GilaMonster (10/26/2011)
Open transaction, no commit. Tran stays open so locks held even though the session was sleeping.
Thanks, but why on tempdb? The SELECT was running on a user database.

I don't know, it's your system 😀

That code you posted is a poor attempt to check for existence of a temp table. It's possible there was a transaction left open from much earlier, from sometime when they were doing something more complex. Transactions once open stay open until there's a commit (of the outer transaction), a rollback or the connection is closed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

GilaMonster (10/26/2011)
Marios Philippopoulos (10/26/2011)
GilaMonster (10/26/2011)
Open transaction, no commit. Tran stays open so locks held even though the session was sleeping.
Thanks, but why on tempdb? The SELECT was running on a user database.
I don't know, it's your system 😀
That code you posted is a poor attempt to check for existence of a temp table. It's possible there was a transaction left open from much earlier, from sometime when they were doing something more complex. Transactions once open stay open until there's a commit (of the outer transaction), a rollback or the connection is closed.

Actually, that makes a lot of sense. The user could have been running something else earlier from the same window within an explicit OPEN tran.

They never committed or rolled back that transaction.

Then they ran the "simple" SELECT statement while the earlier tran was still open. The statement completed quickly, but the tran was still open all these hours and was closed when I killed that SSMS session.

Thanks! 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]