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.
| Author |
Topic |
|
monty
Posting Yak Master
130 Posts |
 Posted - 2006-10-07 : 07:20:34
|
| Hi,I have to shrink the tempdb for reason the size is about 5GB as we had left auto grow option enabled. when i searched microsoft i found some ways out of which i tested the one below:Stop SQL Server. Opened a command prompt, and then started SQL Server by typing the following command: D:\program files>Microsoft sql server>mssql>binn>sqlservr -c -f The -c and -f parameters cause SQL Server to start in a minimum configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB for the log file. altered the TEMPDB files to 1 MB ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1MB) Stop SQL Server by pressing Ctrl-C at the command prompt window, restart SQL Server as a service.This works fine but is there any way where in i can claim spacewith out any down time.. im looking for the best solution.its me monty |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-08 : 07:48:07
|
| If TEMPDB does not resize by itself when you stop/start the SQL Server Service (or reboot!) you almost certainly have a significant problem.My advice woudl be NOT to attempt to SHRINK TEMPDB - there is a high probability of corrupting your database."as we had left auto grow option enabled"This seems like a Good Idea to me (i.e. to allow autogrow), otherwise if some large query causes it to fill up the whole system will be brought to its knees.Sounds to me like you either had one query that needed 5GB working-space, or its a recurring requirement - in which case Shinking is only going to mean TEMPDB growing back to 5GB next time - better to leave it there in the first place!Kristen |
 |
|
|
|
|
|
TempDb