Lower Tempdb Initial file size

Question

Lower Tempdb Initial file size

aby_dvd

Ten Centuries

Points: 1310
More actions
August 18, 2011 at 4:20 am

#241903

I have multiple data files and log files for my database tempdb ( initial size set to 3 gb each )
I want to lower the initial file size now for each file to 1 gb ( not reflecting when i do it through GUI ) 🙁
I want to perform it so that i dont recieve an alert for low disk space in my drive , even when the tempdb files are re-created again ( 3gb*10 = 30 gb < where total disk size is 35 gb >)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

This is how Microsoft suggests you get that job done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

aby_dvd Ten Centuries Points: 1310 More actions · Answer 2

Wrong Link

Bro, This is not what i am looking for .

I want to reduce the initial file size to avoid mis-happenings in Future 🙂

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

If you recreate it with a smaller size, I think that's what you're looking for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

abhishek_dwivedi03 (8/18/2011)
Wrong Link
Bro, This is not what i am looking for .
I want to reduce the initial file size to avoid mis-happenings in Future 🙂

In SQL Server 2008, just go to properties and change it. If you try to make it too small to handle what people are using, it'll change it to the lowest value it can which may not be what you tried for. If you want it lower, then you have to get all of the people out of the system. The only way you can do that is to do what the link Grant posted says.

And his name is "Grant"... not "Bro". 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aby_dvd Ten Centuries Points: 1310 More actions · Answer 5

I am not able to do it even when i am trying to do it in my base machine

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

abhishek_dwivedi03 (8/19/2011)
I am not able to do it even when i am trying to do it in my base machine

Assuming that you mean just changing the Initial Size as I suggested, if it doesn't work, then you'll need to use the methods in the link that Grant provided.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)