How to shrink tempdb in sql 2008 server

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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.

    NAME = tempdev,
    SIZE = 100GB, --adjust size here if needed
    /*, FILENAME = 'd:\new\path\to\tempdb\data\file\tempdev.mdf' */

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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?
Scott PletcherSenior DBACommented:
No.  tempdb cannot be backed up, in fact.
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.
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.
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.