Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB? Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 11:18 AM
Points: 5, Visits: 10
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?
Post #1365343
Posted Thursday, September 27, 2012 9:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:34 AM
Points: 2,609, Visits: 3,903
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..
Post #1365357
Posted Friday, September 28, 2012 7:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1365858
Posted Friday, September 28, 2012 7:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365878
Posted Friday, September 28, 2012 7:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1365884
Posted Friday, September 28, 2012 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
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....



Renuka__
Post #1365887
Posted Friday, September 28, 2012 8:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1365888
Posted Saturday, September 29, 2012 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 9:22 AM
Points: 4, Visits: 35
You can try adding another datafile to the tempdb.
Post #1366193
Posted Saturday, September 29, 2012 10:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1366204
Posted Sunday, September 30, 2012 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1366268
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse