Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to find owner(SPID) of the temp tables Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 3:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

In temp db i find many temp tables created. I would like to know which SPID has created these.
Is there a way to check this?

Regards
Krishna1
Post #1438698
Posted Thursday, April 4, 2013 5:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Not sure we can achive this. Better name your temptable with SPIDs.

sys.dm_db_session_space_usage will give you some details about temp tables' usage.
Post #1438718
Posted Thursday, April 4, 2013 5:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
I am not creating the temp tables. but i still see few created automatically. Hence wahanted to know from where are they getting created
Post #1438723
Posted Thursday, April 4, 2013 5:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 42,763, Visits: 35,855
Short of profiling the instance, I don't think you can tell which session created which table. You can see the temp space that each session uses with the sys.dm_db_session_space_usage DMV.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1438733
Posted Tuesday, April 9, 2013 3:24 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
I suppose you COULD write a server level trigger to append the SPID to the table extended properties. Not sure how useful that would be.
Post #1440586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse