Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clearing SQL 2005/20008 TempDB Expand / Collapse
Author
Message
Posted Friday, October 14, 2011 9:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 25, 2012 2:04 PM
Points: 14, Visits: 243
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.
Post #1190630
Posted Friday, October 14, 2011 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:11 AM
Points: 58, Visits: 178
Why not just shrink it with DBCC SHRINKFILE commands and/or create mulitple files?
Post #1190744
Posted Friday, October 14, 2011 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 25, 2012 2:04 PM
Points: 14, Visits: 243
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!
Post #1190754
Posted Friday, October 14, 2011 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:11 AM
Points: 58, Visits: 178
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
Post #1190782
Posted Friday, October 14, 2011 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 25, 2012 2:04 PM
Points: 14, Visits: 243
That is exactly what I needed. If I find any issues, I will post it. Thanks!
Post #1190786
Posted Monday, October 22, 2012 1:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
thanks guys. also helped me

Ian Cockcroft
MCITP BI Specialist
Post #1375290
Posted Monday, October 22, 2012 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375559
Posted Monday, October 22, 2012 1:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 6,365, Visits: 13,695
Jeff Moden (10/22/2012)
a futile exercise

we are Borg


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1375702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse