SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 TempDB growing at large rate.		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
517 Posts
Posted - 01/07/2013 :  07:22:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 01/07/2013 :  07:44:10  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

United Kingdom
517 Posts
Posted - 01/07/2013 :  07:49:48  Show Profile  Reply with Quote
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

United Kingdom
517 Posts
Posted - 01/07/2013 :  08:52:07  Show Profile  Reply with Quote
What would be the best way around this?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1769 Posts
Posted - 01/07/2013 :  13:09:33  Show Profile  Visit jackv's Homepage  Reply with Quote
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

United Kingdom
517 Posts
Posted - 01/07/2013 :  14:57:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/07/2013 :  23:33:03  Show Profile  Reply with Quote
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

United Kingdom
517 Posts
Posted - 01/08/2013 :  01:47:02  Show Profile  Reply with Quote
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

USA
161 Posts
Posted - 01/08/2013 :  03:45:44  Show Profile  Reply with Quote
Please check this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434
Go to Top of Page

srimami
Posting Yak Master

152 Posts
Posted - 01/16/2013 :  09:41:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1769 Posts
Posted - 01/17/2013 :  01:46:27  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000