How to shrink tempdb in sql 2008 server

Hi,
just recently  been experiencing  some problems with our sql server and also the space in the drive where database is housed has just become almost full.  Noticed the tempdb was very large and is the file that is taking up most of the space.  How would i shrink the file using sql server studio manager?  I was told that restarting the server would essentially bring back the tempdb to its default size but dont really wanna do this now since users are on the server.  Thanks
dankyle67Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You will ultimately have to restart SQL to resize tempdb, even if all the tempdb files stay in the same physical location.

But first, be sure to reset the size of the tempdb file(s), using the command below, to be what you want them to be on startup, and, if you are moving them, where you want them to be.

Yes, 600GB does seem excessive.  If that was caused by a software glitch that's been corrected, you could try starting at, say, 100GB and see it that is enough.  It's better to overestimate rather than underestimate, though, so if you think it's needed, use 200GB or 300GB.

Not sure if you have only one tempdb data file or multiple.  For now, I'll assume one.  If you want to move the file, uncomment the FILENAME = parameter.

ALTER DATABASE tempdb MODIFY FILE (
    NAME = tempdev,
    SIZE = 100GB, --adjust size here if needed
    FILEGROWTH = 100MB
    /*, FILENAME = 'd:\new\path\to\tempdb\data\file\tempdev.mdf' */
    )
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
restarting the server would essentially bring back the tempdb to its default size
And what's the default size?
tempdb database is very important for the performance of the SQL Server. If it reaches the actual size is because it need it. You should think in a better solution rather than shrink. How about moving the tempdb to a dedicated drive with enough space for the actual size? You'll need to restart the SQL Server for that but at least it's for a definitive solution.
0
 
dankyle67Author Commented:
Could you help me with steps on how to move the tempdb and do i restart server after the move or do i move it first.  Something doesnt seem right since the file has always been fairly small and now it suddenly went to 600g!  Thats when we were having a lot of sql runtime errors on our accounting program which uses sql.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dankyle67Author Commented:
Sorry, i misread the actual data size of tempdb, its actually 65g not 650g so will not shrink it.  I found out the actual database that became huge just today is the live database.  It usually is about 65g and it went to 913g overnite and found out that the software people who installed a program 2 days ago which is supposed to fill in data automatically in the accounting fields probably was not working properly and is filling in the data at a crazy rate.  I did notice however that all the system databases like model, master msdb were all backed up recently but the tempdb hasnt been backed up at all.  I must have not included it in the schedule in error so question is, does the tempdb have to be backed up?
0
 
Scott PletcherSenior DBACommented:
No.  tempdb cannot be backed up, in fact.
0
 
Scott PletcherSenior DBACommented:
Btw, if you have any filegrowth set at 10%, use commands like above to adjust that to a fixed amount.  I've found that 10% can run significantly slower, esp. for log files.
0
 
dankyle67Author Commented:
Wow never knew that you cant back up the tempdb.  I'm not sure what filegrowth is set at since im not the one who configured the sql server when they got it about a year ago.  I can locate these in sql server studio correct?  Im just more comfortable using that.  I do however routinely monitor filesizes manually  to make sure backup jobs are backing up approximate size from previous past week to make sure nothing grows suddenly as in this case but i was not alerted to this until only this morning.
0
 
Scott PletcherSenior DBACommented:
You can check autogrowth in the GUI (ugh!  I'm a DBA, so I don't use the GUI for db administration tasks :-) ).

Right-click on the db; select "Properties"; then "Files" on the left of the screen.  You should see an "Autogrowth" column.  If it says "%", then change it to a fixed amount.
0
 
dankyle67Author Commented:
I figured you would say something to that extent.  I don't blame your outlook towards GUI but it's all i know.  Thanks for the helpful information, it will come in handy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.