<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Current tempDB size at 2 GB. Anyway I can...
11 posts, Page 1 of 2
1
2
»»
Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB?
Rate Topic
Display Mode
Topic Options
Author
Message
acorrei1
acorrei1
Posted Thursday, September 27, 2012 9:12 AM
Forum 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
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Thursday, September 27, 2012 9:47 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 9:03 AM
Points: 2,415,
Visits: 3,374
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
Bhuvnesh
Bhuvnesh
Posted Friday, September 28, 2012 7:25 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1365858
Jeff Moden
Jeff Moden
Posted Friday, September 28, 2012 7:46 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1365878
Bhuvnesh
Bhuvnesh
Posted Friday, September 28, 2012 7:59 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1365884
Renuka__
Renuka__
Posted Friday, September 28, 2012 8:05 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 966,
Visits: 1,251
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
Jeff Moden
Jeff Moden
Posted Friday, September 28, 2012 8:05 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1365888
SSVN
SSVN
Posted Saturday, September 29, 2012 8:41 AM
Forum 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
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Saturday, September 29, 2012 10:23 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317,
Visits: 9,216
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
Jeff Moden
Jeff Moden
Posted Sunday, September 30, 2012 10:00 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1366268
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
