We help IT Professionals succeed at work.

How to shrink tempdb in sql 2008 server

dankyle67
dankyle67 asked
on
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
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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.

Author

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.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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' */
    )

Author

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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
No.  tempdb cannot be backed up, in fact.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

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

Author

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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

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

Author

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.