transaction log growth in tempdb

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

transaction log growth in tempdb

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-07-27 : 07:12:45

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.

muratos
Starting Member

22 Posts

Posted - 2011-07-29 : 04:00:35

I don't know much about the first question but have ideas on the second. Log files record all the changes. So, a simple delete from statement without any where clause for a very large table would easily explode the log file until it completes. Or a very big transaction without reaching to commit would do the same. I am not expert but talk according to experience. I hope it is useful for you.

http://ipad2keyboard.net
http://ipad2covers.org

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-10 : 17:58:32

Thanks for the input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources