Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb issue


Tempdb issue

Author
Message
solomon.jernas
solomon.jernas
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 979
we are facing issue in tempdb file size , initial size we allocate 2 GB , but after restart sql server , size would be 1 MB , its gone 2 GB, plz anyone help , How size will keeping on 2 GB after restart sql server ..

Any settings ?







Thanks
Jerry
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1921
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.

I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.

Joie Andrew
"Since 1982"
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Joie Andrew (7/8/2013)
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.

I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.


Hi Joie

I think the OP means that he set the initial size of his TempDB datafile to 2GB, restarted SQL Server and the size went back to 1MB.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36365 Visits: 18759
If you grow the files, they should be at that size on restart. Are you sure you manually grew them and didn't just assume they'd stay the same because they grew from activity?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
solomon.jernas
solomon.jernas
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 979
Any settings need to be do on Model database ?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
No.

What exactly happened and why is it a concern?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1921
Joie Andrew (7/8/2013)
TempDb is used for several things in SQL Server. DBCC CHECKDBs, temp tables, row versioning, etc. If TempDb keeps getting to 2GB in size I would say that is a good indicator of the size it needs to be in order to handle all of the temporary objects that is being requested of it on a routine basis.

I would recommend changing the default size of TempDb from 1MB to 2GB to prevent the growth actions after SQL Server is restarted.


Hi Joie

I think the OP means that he set the initial size of his TempDB datafile to 2GB, restarted SQL Server and the size went back to 1MB.


Ah yes, I totally turned that around in my head when I read it.Ermm

Thanks for the clarification!

Joie Andrew
"Since 1982"
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 923
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.
Steve-3_5_7_9
Steve-3_5_7_9
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1584
kevaburg (7/9/2013)
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.



It absolutely is something to be concerned about. You want to have TempDB properly sized, so in this case the OP would want to size it at 2GB or higher. If you allow TempDB to "autogrow" you will most likely have performance issues as the TempDB file(s) grow.

Steve



ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3956 Visits: 6693
After you issue the command(s) below, the next time SQL starts, tempdb will be sized accordingly.

Split the 2GB into equal parts, for however many tempdb data files you have.

For this example, I'll assume you have 2 data files -- if not, adjust accordingly.



ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev, SIZE = 1GB )

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev2, SIZE = 1GB )

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = 512MB ) --<< or whatever values matches your max expected usage




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search