December 27, 2011 at 12:39 pm
My temp db got really big 200 GB,how can I find out why?
I restarted sql server and it got back to 52 gb small db
Thank you
December 27, 2011 at 1:04 pm
You have a query or queries that are requiring significant space in tempdb. Since tempdb is recreated each time you restart the server it will always start out at its initial size and will grow according to what is happening on the server. Keep an eye on it and if it grows again you'll have to do some tracing / auditing to find the ones causing the issues.
Check this out for more information: http://msdn.microsoft.com/en-us/library/ms175527%28v=SQL.90%29.aspx
December 27, 2011 at 1:10 pm
The same principles from this article will help you with the tempdb growth you are seeing.
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
December 27, 2011 at 11:08 pm
In addition to above, check if the SORT_IN_TEMPDB option is set to ON while rebuilding indexes than SQL Server will use tempdb.
Check following article for further detail.
http://msdn.microsoft.com/en-us/library/ms188281.aspx
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 27, 2011 at 11:54 pm
tempdb with size 200 gb .it seems that server might be production or higher region-ed.so restart should be avoided. are you managing tempdb well like multiple files on multiple disks ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 28, 2011 at 8:29 am
While it is generally a good idea to have tempdb spread across multiple files/drives, this would not directly address the poster's issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply