Clearing SQL 2005/20008 TempDB

Question

Post reply

Clearing SQL 2005/20008 TempDB

tpalmer

SSC Eights!

Points: 962
More actions
October 14, 2011 at 9:40 am

#258867

We have an alert that allows us to monitor the system TempDB on SQL 2005 and 2008 instances. The monitor basically monitors the TempDB and reports back the LogSpaceUsed% if for 30 straight minutes the threshold is over 60% [fyi, I runs DBCC SQLPERF ('logspace')behind the scenes]. The LogSpaceUsed% value does go down, but never to an acceptable level (for instance the log file size is 16 GB and 8 GB or 50% never seems to clear).
How do I go about finding what exactly is in the Temp DB using these resources so I can start to troubleshoot the calls? What is the best way of tackling finding out the contents.
Any thought or help would be appreciated.

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

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

brekher SSCommitted Points: 1514 More actions · Answer 1

Why not just shrink it with DBCC SHRINKFILE commands and/or create mulitple files?

tpalmer SSC Eights! Points: 962 More actions · Answer 2

I do not think you understand. Why shrink the Temp DB? Llet me start again.

System TempDB has 1 LDF device of 16 GB. We get alerts when the TempDB Usage is over 60%, meaning there are transaction using the TempDB. Once a transaction is finished, it should clear out of the log. I am looking for help finding the best way to see what is actually using resources in the TempDB log at any given time. I am not looking shring Temp DB (and this is definitely not recommended practice from Microsoft). I want the 16 GB log file, I just want to see if I can find out what is using the space.

"Hmmm. Lets go see what is utilizing the TempDb right now."

Thx!

brekher SSCommitted Points: 1514 More actions · Answer 3

give this a shot, hope it helps!

http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/11/30/what-s-using-space-in-tempdb.aspx

tpalmer SSC Eights! Points: 962 More actions · Answer 4

That is exactly what I needed. If I find any issues, I will post it. Thanks!

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 5

thanks guys. also helped me

Ian Cockcroft
MCITP BI Specialist

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

brekher (10/14/2011)
Why not just shrink it with DBCC SHRINKFILE commands and/or create mulitple files?

How about because if you try to do a shrink on temp db while the server is in other than a single user mode, you could end up corrupting databases.

Also, shrinking Temp DB is normally a futile exercise unless you've found out and fixed whatever made it grow beyond expectations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Jeff Moden (10/22/2012)
a futile exercise

we are Borg 😀 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉