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 datafile initial size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter99
Constraint Violating Yak Guru

494 Posts

Posted - 01/09/2013 :  15:45:19  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/09/2013 :  19:58:52  Show Profile  Reply with Quote
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

USA
203 Posts

Posted - 01/10/2013 :  23:17:51  Show Profile  Visit prett's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/11/2013 :  11:46:27  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/11/2013 :  18:54:19  Show Profile  Reply with Quote
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 - 01/15/2013 :  07:30:41  Show Profile  Reply with Quote
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
  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.05 seconds. Powered By: Snitz Forums 2000