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 datafile initial size

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."
Go to Top of Page

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 = 6
TRUNCATEONLY = gives the reclaimed space to OS.

Hope it will work for you also!!
Go to Top of Page

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.
Go to Top of Page

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 = 6
TRUNCATEONLY = 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."
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -