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 |
|
Peter99
Constraint Violating Yak Guru
498 Posts |
 Posted - 2013-01-09 : 15:45:19
|
| Hi,I have tempdb datafile initial size setup for 30GB. That consumed lot of disk space. I am receiing out of disk space issue. I want to reduce initial size to 500mb. 98% free space available in this data file. I tried to change initial size through db properties, alter database modify file, shrink database but no success.Is there any way how to change tempdb datafile initial size to some lower value?Thanks |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 19:58:52
|
Yes, but you would have to shrink TempDB first. In order to do that, you really need to be in the single-user mode to prevent any corruption of databases when whrinking TempDB... it's just one of those things. One way or another, if you do it right, it's going to require an outage.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
 |
|
|
prett
Posting Yak Master
212 Posts |
Posted - 2013-01-10 : 23:17:51
|
| This command worked for me:dbcc shrinkfile ('tempdev',6,TRUNCATEONLY)Traget size in MB = 6TRUNCATEONLY = gives the reclaimed space to OS.Hope it will work for you also!! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-11 : 11:46:27
|
| Why you want to change initial size? It will grow again. Shrink the db and make sure you have enough space. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-11 : 18:54:19
|
quote:
Originally posted by prett
This command worked for me:dbcc shrinkfile ('tempdev',6,TRUNCATEONLY)Traget size in MB = 6TRUNCATEONLY = gives the reclaimed space to OS.Hope it will work for you also!!
You got very lucky. Lot's of folks report some major problems when they try to shrink TempDB in the multi-user mode. I wouldn't make a regular practice of it.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-01-15 : 07:30:41
|
| Hi Peter,Q: Is the initial size of tempdb set to 30Gb or is it grown to 30GB, please clarify.If it has grown to 30GB, you can simply restart the sql server to reclaim the space back to disk. If the initial size is set to 30GB then you have no option except use DBCC ShrinkFile command.Thanks,Sri. |
|
|
|
|
|
|
|
|
Tempdb datafile initial size