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 is huge and will not come down:how recreate

Author  Topic 

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-08-09 : 11:49:49
On MS SQL 2008 R2

Our TempDB has grown to 75 gigs and the Unused space is about 73 gigs. I tried Shrink and the DBCC shrink too then restarted server. That did nothing.

I then stopped the server and renamed the tempdb files and restarted the server, I saw the a new tempdb file was generated but he log wasn't and the server would not come back up saying that RPC was not available, or just not giving any error and not starting at all.

So I renamed the original files back and now the server started. However the size is huge and I need to reset it.

My question is, how can I start Sqlserver in a mode that will regenerate tempdb? I saw that a command line option is availbale: sqlservr -f -c, but I'm not sure if that would mess other things up.

also, once in command line mode, can the tempdb be recreated somehow?

Thanks!

--PhB

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-09 : 12:42:49
Look up what the initial sizes for the data and log files are. That will be the minimum size even if you restart, if they are large, that is the problem. It's in MegaBytes.

If that is the case (or even if that isn't) then look at the methods here ( http://support.microsoft.com/kb/307487
some of which at least you said you tried). Since you are allowed to restart the server, the first method should work if you don't have additional files. But be sure to do it exactly as they describe it there.

The -c and -f are harmless from what I know. http://technet.microsoft.com/en-us/library/ms190737.aspx After you are done, be sure to control-c the command line and then restart the server normally.
Go to Top of Page
   

- Advertisement -