It's a SQL Server 2012 EE.

One session (SID=90) executed a JOIN query, and it's generated a temp table internally by SQL Server.

But another sessions (SID=103, 109, 110,116) from totally different app ran a query, and blocked by above session due to the same temp table!

As a result several blocking occurred. (Pls refer to the screenshot) How it could be happened? I can't understand and lost mind how to fix it. enter image description here

The first session executed:

SELECT ... FROM Institution institutio0_ 
left outer join ImageFile imagefile1_ 
on institutio0_.ImageFileId=imagefile1_.Id 
WHERE institutio0_.Id=@p0

And blocked sessions executed a same query:

select EventClass, LoginName, StartTime, FileName, ObjectID, IndexID, ObjectType, ObjectName, EventSubClass, Success, case when ParentName is not null then ParentName else OwnerName end as OwnerName, .... SCHEMA_NAME( OBJECTPROPERTY( ObjectID, 'SchemaId' ) ) DerivedOwnerName, OBJECT_NAME( ObjectID ) DerivedObjectName 
from ::fn_trace_gettable(  @P0 , default ) 
where StartTime >  @P1  
and StartTime <=  @P2  
and DatabaseID =  @P3 

Any idea?

  • Why are both sessions using the same temp table? – McNets Oct 21 '19 at 14:52
  • @McNets Yes, that's my question, how it's possible? The first one is a long running OUTER JOIN query, and system create the temp table. And second query, too. But both are same name of temp table! – jelongpark Oct 21 '19 at 19:14
  • Can't you change the name of one of them? – McNets Oct 21 '19 at 19:15
  • @McNets No. The temp table name is internally created by sql server engine when the JOIN query was running. But the blocked sessions, I have no idea how/why try to lock the same temp table. – jelongpark Oct 23 '19 at 14:13
  • Is there any stored procedure involved? – McNets Oct 23 '19 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.