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

Tempdb issue Expand / Collapse
Author
Message
Posted Sunday, July 7, 2013 11:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 6, 2014 9:55 PM
Points: 157, Visits: 846
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
Post #1471042
Posted Monday, July 8, 2013 2:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 3:46 AM
Points: 709, Visits: 1,442
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"
Post #1471083
Posted Monday, July 8, 2013 6:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:08 AM
Points: 1,380, Visits: 2,703
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.
Post #1471150
Posted Monday, July 8, 2013 10:37 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 7:12 AM
Points: 31,284, Visits: 15,746
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
Post #1471279
Posted Monday, July 8, 2013 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 6, 2014 9:55 PM
Points: 157, Visits: 846
Any settings need to be do on Model database ?
Post #1471293
Posted Monday, July 8, 2013 11:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:08 AM
Points: 40,415, Visits: 36,864
No.

What exactly happened and why is it a concern?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1471295
Posted Monday, July 8, 2013 8:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 3:46 AM
Points: 709, Visits: 1,442
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.

Thanks for the clarification!


Joie Andrew
"Since 1982"
Post #1471416
Posted Tuesday, July 9, 2013 1:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 15, 2014 12:45 AM
Points: 328, Visits: 544
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.

Post #1471454
Posted Wednesday, July 10, 2013 5:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:50 AM
Points: 922, Visits: 1,444
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



Post #1472064
Posted Wednesday, July 10, 2013 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1472241
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse