TempDB File Size Per TempDB FIle

Question

TempDB File Size Per TempDB FIle

SQLDude

SSC-Addicted

Points: 409
More actions
March 27, 2019 at 9:07 am

#2033921

I'm currently setting up multiple SQL Server 2017 instances using a configuration file. I have the TempDB separated into the recommended 8 files and have the TempDB disk is 100GB in size. My question is, do I need to go an alter each of the 8 files or do I just use the ALTER DATABASE TempDB... for the original file? I would like to also turn off autogrowth. Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLDude SSC-Addicted Points: 409 More actions · Answer 1

I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.

ALTER DATABASE tempdb MODIFY FILE (Name=<filename.mdf>, Size = 12250); GO ALTER DATABASE tempdb MODIFY FILE (Name=<filename.ndf>, Size = 12250); GO

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

trent.adams0201 - Wednesday, March 27, 2019 11:33 AM
I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.
ALTER DATABASE tempdb MODIFY FILE (Name=<filename.mdf>, Size = 12250); GO ALTER DATABASE tempdb MODIFY FILE (Name=<filename.ndf>, Size = 12250); GO

That only leaves you 2GB for the LDF... could be a form of "Death by SQL".

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

SQLDude SSC-Addicted Points: 409 More actions · Answer 3

@jeff Moden

Jeff Moden wrote:

trent.adams0201 - Wednesday, March 27, 2019 11:33 AM
I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.
<span id="if_insertedNode_1553707938884">
ALTER DATABASE tempdb
MODIFY FILE (Name=<filename.mdf>,
Size = 12250);
GO
ALTER DATABASE tempdb
MODIFY FILE (Name=<filename.ndf>,
Size = 12250);
GO</span>
That only leaves you 2GB for the LDF... could be a form of "Death by SQL".

Thanks Jeff, I've made changes accordingly.

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

Thanks for the feedback.

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