July 27, 2011 at 5:13 am
Hi Friends,
I am trying to know if there is any query which tells us this particular SPID(s) or session(s) are using TEMPDB.
For example,
I have 2 sessions 53,54 and trying to create objects in tempdb. Now my query has to show me tempdb is currently being used by spids 53,54
and along with TSQL statement being executed. Am using this below sql stmt to pull the sql statement but am looking for something like spid and sql and which is using the tempdb
SELECT spid, blocked,
program_name,
ISNULL(nt_username,' '),
(CASE WHEN (st.text IS NULL) THEN Isnull(st.text,'--') ELSE st.text END)
FROM master..sysprocesses
LEFT JOIN SYS.DM_EXEC_REQUESTS dm on dm.session_id=spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(dm.sql_handle) AS st
WHERE spid =
is there any direct query to achieve that?
Example)
spid = 53
----------
use db1
go
create table t2
(id int)
insert into t2 select 156
create table #temp_tbl
(
id int
)
go
declare @i int
set @i = 1
while @i > 0
begin
insert into #temp_tbl
select @i
end
spid = 54
----------
use db2
go
create table emp
(id int)
insert into t2 select 156
create table #sample_tbl
(
id int
)
go
declare @i int
set @i = 1
while @i > 0
begin
insert into #sample_tbl
select @i
end
Now i want to know a query which display spids 53,54 are using tempdb and i want to display the entire sql batch executing in the spids and what is sql which is getting executed in tempdb currentl.
Basically i am looking for a report using query to see the tempdb usage.
Also, one more thing is , in what all scenario's the tempdb .mdf file remains constant size but the ldf file grows dramatically.
I have scenario where tempdb.mdf is 1 gig but tempdb.ldf grows to 55 gigs.
Finally i managed to shrink it back to 160 mb.
Any help would be greatly appreciated.
Thank You.
July 27, 2011 at 5:36 am
Search for Adam Machanic's sp_WhoIsActive.
It'll give you that info and a ton more.
July 27, 2011 at 5:40 am
You can also use sys.dm_db_session_space_usage DMV to find out which session is using/has used tempdb including IO done in tempdb.
July 27, 2011 at 5:49 am
Here's a demo. The whole topic is really advanced, but see the query he uses to get tempdb space usage (really simple) :
July 27, 2011 at 7:55 pm
Thanks a lot 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply