Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB?

Question

Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB?

acorrei1

SSC Veteran

Points: 223
More actions
September 27, 2012 at 9:12 am

#393268

I'm not a DBA but I've been tasked with this change for the simple fact we don't have a DBA at the moment. The initial size of our TempDB is set at 2GB. Is there any way I can set TempDB to have it's initial size start at 100 MB instead?

Viewing 11 posts - 1 through 10 (of 10 total)

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Do you have that much tempdb operations happening to use 2GB?? If not try reducing the size of tempdb files and see how SQL Server behaves..

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

If thats a non-production environment where restart the sql services is possible. set the size of MODEL database. it will be get inherited when sql gets restarted

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Bhuvnesh (9/28/2012)
If thats a non-production environment where restart the sql services is possible. set the size of MODEL database. it will be get inherited when sql gets restarted

So if you want a 12GB TempDB, are you suggesting to set the otherwise unused Model database to 12GB? Doesn't sound right to me. It's better to properly size TempDB in properties.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Jeff Moden (9/28/2012)So if you want a 12GB TempDB, are you suggesting to set the otherwise unused Model database to 12GB? Doesn't sound right to me. It's better to properly size TempDB in properties.

jeff, i didnt get your point here , what i suggest is , set the model at 100MB so that tempdb initial size would be 100 ma at restart.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Renuka__ Hall of Fame Points: 3656 More actions · Answer 5

Hello,

You can modify the tempdb size by going to the properties (right click Tempdb and properties); change the initial zise and the growth; The values should take effect when you restart SQL services.

Also, check these commands:

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE=<size of the data file>);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, SIZE=<size of the log file>);

GO

Restart SQL services for the changes to take effect....

[font="Verdana"]Renuka__[/font]

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

Bhuvnesh (9/28/2012)
Jeff Moden (9/28/2012)So if you want a 12GB TempDB, are you suggesting to set the otherwise unused Model database to 12GB? Doesn't sound right to me. It's better to properly size TempDB in properties.
jeff, i didnt get your point here , what i suggest is , set the model at 100MB so that tempdb initial size would be 100 ma at restart.

Model is already set pretty small so that actually might not do any good. You need to set the intial size of TempDB through the properties of the TempDB database.

The other problem here is that if you set it to 100MB and you have processes that need it to be 2GB, it's a futile act that will also cause delays in the process while TempDB grows to meet the demand.

Also, the default settings for TempDB growth are terrible and will cause much fragmentation. The growth properties need to be changed anyway.

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

SSVN SSC Rookie Points: 32 More actions · Answer 7

You can try adding another datafile to the tempdb.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Wow - so much mis-information...

Tempdb will be resized to its initial size upon restart. If it has grown to 2GB over time - then that is the size it needs to be. If the initial size was set to 2GB, then changing the model database, shrinking the database, or any other options are not going to change this.

To change the initial size, you have to put SQL Server into single-user mode. Then, you can resize the files using SHRINKFILE with a new size. Once that is done, the initial size will be reset and when you restart SQL Server it will resize tempdb back to the new initial size.

Note: shrinking tempdb while users are accessing the system could cause corruption in your databases. It is not recommended and should not be attempted unless you can insure that no users are accessing it. That is why you need to put SQL Server into single-user mode.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Jeffrey Williams 3188 (9/29/2012)
Wow - so much mis-information...
Tempdb will be resized to its initial size upon restart. If it has grown to 2GB over time - then that is the size it needs to be. If the initial size was set to 2GB, then changing the model database, shrinking the database, or any other options are not going to change this.
To change the initial size, you have to put SQL Server into single-user mode. Then, you can resize the files using SHRINKFILE with a new size. Once that is done, the initial size will be reset and when you restart SQL Server it will resize tempdb back to the new initial size.
Note: shrinking tempdb while users are accessing the system could cause corruption in your databases. It is not recommended and should not be attempted unless you can insure that no users are accessing it. That is why you need to put SQL Server into single-user mode.

+1. And, you should still change the awful default growth settings to something more reasonable.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

Jeff Moden (9/30/2012)

+1. And, you should still change the awful default growth settings to something more reasonable.

Absolutely....

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs