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 is huge and will not come down:how recreate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 08/09/2013 :  11:49:49  Show Profile  Reply with Quote
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

Edited by - phrankbooth on 08/09/2013 11:53:37

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 08/09/2013 :  12:42:49  Show Profile  Reply with Quote
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
  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.03 seconds. Powered By: Snitz Forums 2000