June 30, 2011 at 2:05 am
Hello -
Not sure if I am asking the correct question here. I have two different DBs (flash and light) and user is creating some temporary tables in both DBs. All the temp tables are getting stored in TempDB. My question here is - how do we know that temp table 'A' has been created in which DB?
For example:-
User creates a temp table in 'flash' database -
create table #authors
(au_id char (11))
User creates a temp table in 'light' database -
create table #books
(au_id char (11))
Both above mentioned temp tables (#authors and #books) get created in TempDB. I wanted to know from which database, these tables have come from (flash or light).
How do we know, somehow, database name where these tables were created?
Thanks,
Sanjeev.
June 30, 2011 at 2:07 pm
Not that I know of. Why do you need it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 2:22 pm
Keep in mind temp tables will always be created in tempdb. I am also curious why you care what database they came from? You can't do anything with them from outside the sproc or sproc chain that they are in. Do tell..
CEWII
June 30, 2011 at 2:43 pm
Jonathan Kehayias has code that uses the default trace to get the SPID associated with a temp table. Join that with sysprocesses and you've got the database.
DECLARE @FileName VARCHAR(MAX)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
o.name,
o.OBJECT_ID,
o.create_date,
gt.NTUserName,
gt.HostName,
gt.SPID,
gt.DatabaseName,
gt.TEXTData,
db_name(p.dbid)
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
ON gt.ObjectID = o.OBJECT_ID
JOIN sysprocesses as p
ON p.spid = gt.spid
WHERE gt.DatabaseID = 2
AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
Hope this helps.
June 30, 2011 at 2:48 pm
That does not seem to work in terms of finding the correct database name. It always says tempdb for me 🙁
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 2:50 pm
The last column? (Not the column called DatabaseName, which for the original poster's purposes, can be omitted.) The last column is the dbid associated with the spid.
June 30, 2011 at 2:52 pm
Still, I want to know WHY..
CEWII
June 30, 2011 at 2:53 pm
It's always 2, the id for tempdb, for me.
LoginName & NTUserName are proper which is nice. Maybe one of those could be used to infer the DB name, depending on the scenario.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 2:54 pm
Elliott Whitlow (6/30/2011)
Still, I want to know WHY..CEWII
Me too 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 2:57 pm
Really? I get the database associated with the spid. Tried it on multiple versions, even. Interesting.
To eliminate the extraneous:
DECLARE @FileName VARCHAR(MAX)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
o.name,
o.OBJECT_ID,
o.create_date,
gt.NTUserName,
gt.HostName,
gt.SPID,
db_name(p.dbid) as DatabaseName
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o
ON gt.ObjectID = o.OBJECT_ID
JOIN sysprocesses as p
ON p.spid = gt.spid
WHERE gt.DatabaseID = 2
AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
June 30, 2011 at 3:24 pm
Got it now 🙂
Use sys.sysprocesses for 2005+
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 1:34 am
My sincere apologizes for not writing back the reason why I want to know.
I have a custom application which is in clustered/shared environment. My client complaining that since the TempDB is in shared environment, they can't restart the services everytime. So I was planning to write a small program which will delete temp tables which are created from my database. Probably the example I gave would help you to understand (I own flash database).
BTW, I have not tried the query provided. I will try today and will let you all know.
Thanks,
Sanjeev.
July 5, 2011 at 1:53 am
And It worked for me ...
Thanks Colleen M. Morrow for guiding me to the correct article ...
Thanks,
Sanjeev.
July 5, 2011 at 1:55 am
why do your clients need to keep restarting SQL server?
July 5, 2011 at 1:55 am
Sanjeev
I don't know of any way of deleting other people's temp tables. I tried it once, without success. Even if there is a way, the best option is to design your application so that temp tables are removed as soon as they have been used.
John
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply