TempDB Log Growth Help

Question

Post reply

TempDB Log Growth Help

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
January 20, 2012 at 2:48 pm

#391367

Hey everyone,
Just looking for something obvious that I am missing. Tempdb LDF file is growing roughly 100GB in about 24 hours. I can't see why.
Facts.
1) 2005 SP4 64bit Standard Edition SQL Server
2) LDF/MDF are on seperate drives from ALL of the other databases
3) TempDB is in SIMPLE recovery
4) DBCC OpenTran does not show ANY long running transactions that would cause the log to fillup
5) DMV's are showing a waittype of WRITELOG. In talking with Network Admin, there is nothing wrong with the disks.
6) I am not seeing any blocking for the SPID's with WRITELOG waittype.
Anyone got any thoughts on other things that might be causing the issues and how I can track them down?
Thanks,
Fraggle

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

padhis SSCommitted Points: 1762 More actions · Answer 1

analyze which Transact-SQL statements are the top consumers of tempdb

SELECT t1.session_id, t1.request_id, t1.task_alloc,

t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,

t2.statement_end_offset, t2.plan_handle

FROM (Select session_id, request_id,

SUM(internal_objects_alloc_page_count) AS task_alloc,

SUM (internal_objects_dealloc_page_count) AS task_dealloc

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id) AS t1,

sys.dm_exec_requests AS t2

WHERE t1.session_id = t2.session_id

AND (t1.request_id = t2.request_id)

ORDER BY t1.task_alloc DESC

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here is an article outlining a method to help find what causes the growth.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What's the value of log_reuse_wait_desc for tempDB?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

GilaMonster (1/22/2012)
What's the value of log_reuse_wait_desc for tempDB?

NOTHING is what it is set to. So pretty much a completely useless description.

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

padhis (1/22/2012)
analyze which Transact-SQL statements are the top consumers of tempdb
SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC

This doens't show any SQL Handles or Plans. I am guess this is probably due to the sever restart, so I will wait until it starts to get busy on Monday.

Fraggle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Is that what it is now, or what it was at the time the log was growing?

If now, that's not going to tell you anything, you need to check what it is at the point the log is growing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Fraggle-805517 (1/22/2012)
I am guess this is probably due to the sever restart

You didn't mention you'd restarted the server...

Any evidence of what caused the growth would have been erased by the recreation of tempDB when SQL restarted. You're going to have to wait until the problem reoccurs before you can do any diagnostics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

GilaMonster (1/22/2012)
Fraggle-805517 (1/22/2012)
I am guess this is probably due to the sever restart
You didn't mention you'd restarted the server...
Any evidence of what caused the growth would have been erased by the recreation of tempDB when SQL restarted. You're going to have to wait until the problem reoccurs before you can do any diagnostics.

In the meantime, enable the monitoring described in the article I referenced. That will permit you to research the issue even if it is not occurring at the moment or even if you restarted the server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events