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)
 TempDB growing at large rate.

Author  Topic 

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-07 : 07:22:52
Hello there.

Every now and then my tempdb grows at a large rate in such a small space of time.

I want to automate a process at the weekend to bring down the space used by TempDB.

what are the common methods or tricks i could use.?

Regards

Rob

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 07:44:10
Why do you want to do that?
Do you know what is causing it?
If not and it happens regularly then it is best to leave it at it's max size.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-07 : 07:49:48
yes i know what is causing, but just want a temporary solution so disk space doesnt run out.
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-07 : 08:52:07
What would be the best way around this?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-07 : 13:09:33
do you maintain the Tempdb on a dedicated disk? If so, then leaving it at max size is OK .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-07 : 14:57:52
no we have the tempbd on same as all other databases.
we have never experienced this problem. don't want to have to keep
restarting services. what other options do I have?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-07 : 23:33:03
You will need to get more space for TempDB. It is used in Various activities like temp table,Online Rebuild...Ordering and versioning. What is the size of the file?
Go to Top of Page

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-08 : 01:47:02
it is currently 5 gig.

what activities would free up some space.

ie. dropping temp tables, killing active queries
Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2013-01-08 : 03:45:44
Please check this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-01-16 : 09:41:59
You can shrink tempdb using DBCC Shrinkfile command if you do not want to restart the services every time
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-17 : 01:46:27
As well as suggestions made , monitor activity in TempDB , are there , for example, large SORTS that should not be occuring in the TempDB?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -