Reduce INITIAL SIZE of TEMPDB

Question

Reduce INITIAL SIZE of TEMPDB

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

mjsteele SSChasing Mays Points: 629 More actions · Answer 1

Don't feel dumb, that piece took me a while to figure out. In Mgmt Studio, go to Tools --> Options. In the At Startup, select "Open a new query window". Restart Mgmt Studio and it will open only a query window.

sheridan-499329 SSC Veteran Points: 227 More actions · Answer 2

Never mind - I found it too.

In case someone else comes here and looks for an answer, if you don't have your SQL Server set up to start up as 'Open New Query Window', you'll need to set that option to avoid the 'single user' error message. To change to this option, follow these steps:

1) Before stopping SQL Server manually and restarting via the command line, go to Tools > Options.

2) Under Environment > General, choose 'Open new query window' under 'At Startup'.

3) Stop the server manually and start it via the command line.

4) Launch another session of SQL Server; your query analyzer window should pop up.

5) Type in your ALTER DATABASE command; it should run without problem.

6) Follow the directions to stop the command line (Ctrl-C) and restart manually.

Hope this helps.

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 3

After you use Shrink+Alter to reduce the size, if you issue another alter command to increase the size, does that become the 'new' initial size?

Jagdev Mr or Mrs. 500 Points: 552 More actions · Answer 4

This thing didnt worked for me. accidently i had put initial size of tempdb to 14000MB instaed of 1400 MB.

Now i want to decrease the size of tempdb without restarting sql services. But no luck.

Jagdev Mr or Mrs. 500 Points: 552 More actions · Answer 5

Hi Matt

DBCC shrink and ALter didnt worked for me.

Is there any other way to reduce initial size?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Beware shrinking tempdb: http://support.microsoft.com/kb/307487

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Tara-1044200 SSCoach Points: 15948 More actions · Answer 7

I am getting the following error because becaseu tempdb size is more than the actual drive space and i am unable to start the server though i can start using "NET START MSSQLServer /f /T3608"

Could not find database ID 2. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

Now, i can not alter the tempdb size as it is not found, any help?