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

Adding an extra file to TEMPDB Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 10:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
GilaMonster (2/28/2013)
[quote][b]I meant exactly what I said. No reboot necessary. A restart of the SQL Server service is very strongly recommended. A reboot of the entire server is not required at all.


Cool. then I agree completely.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425187
Posted Thursday, February 28, 2013 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
ScottPletcher (2/28/2013)
So you don't use a startup proc to verify tempdb data files are all the same size and sync them if they are not?

How, then, do you become aware of and correct the situation where one or more, but not all, of the tempdb data files have automatically extended? Maybe there is some other method of which I am not aware and could use.


Read my whitepaper linked above and you'll have your answer, or at least my version of it.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425191
Posted Thursday, February 28, 2013 10:48 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 @ 10:05 AM
Points: 42,829, Visits: 35,961
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.


Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.



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 #1425192
Posted Thursday, February 28, 2013 10:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.


Or maybe you did read my whitepaper. This is what I recommend. Pre-size your tempdb files out to nearly the full extent of the drive and then disable auto-growth on the files.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425194
Posted Thursday, February 28, 2013 10:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
GilaMonster (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.


Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.



I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically? Interesting. Only talking about increased size here, never shrinking.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425196
Posted Thursday, February 28, 2013 10:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.


Or maybe you did read my whitepaper. This is what I recommend. Pre-size your tempdb files out to nearly the full extent of the drive and then disable auto-growth on the files.



Zero chance. I'd never disable autogrow on tempdb: you could slam your entire instance to a halt -- or worse, perhaps cause it to crash.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425197
Posted Thursday, February 28, 2013 10:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
ScottPletcher (2/28/2013)
[quote]

So proportional fill will "ensure" that? I wasn't sure that was guaranteed. I know it wasn't in the past.


Nobody is saying that proportional fill will ensure that the files are all the same size at startup. We are saying that having the same defined size will ensure that they are the same size at startup even if 1 file auto-grew and others didn't because the files are reset to the defined size.

This has been the case since at least SQL 2005. I think it was the case with SQL 2000 as well, but I can't recall clearly enough to be sure. So yes, we are saying that what you thought you saw previously is not actually what happened.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425200
Posted Thursday, February 28, 2013 10:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 1,616, Visits: 1,543
ScottPletcher (2/28/2013)
Zero chance. I'd never disable autogrow on tempdb: you could slam your entire instance to a halt -- or worse, perhaps cause it to crash.


If you've pre-sized the files to consume almost the entire drive, there's no room for auto-growth. Where are you expecting an autogrowth to expand to?




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1425203
Posted Thursday, February 28, 2013 11:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
Zero chance. I'd never disable autogrow on tempdb: you could slam your entire instance to a halt -- or worse, perhaps cause it to crash.


If you've pre-sized the files to consume almost the entire drive, there's no room for auto-growth. Where are you expecting an autogrowth to expand to?



I would never do that either. I size tempdb to match what SQL needs, not what happens to be the current physical drive size, which of course is completely unrelated to SQL's requrements.

Besides, I sometimes put tempdb files on different drives, to balance drive I/O, space usage, etc..


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425206
Posted Thursday, February 28, 2013 11:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
Robert Davis (2/28/2013)
ScottPletcher (2/28/2013)
Zero chance. I'd never disable autogrow on tempdb: you could slam your entire instance to a halt -- or worse, perhaps cause it to crash.


If you've pre-sized the files to consume almost the entire drive, there's no room for auto-growth. Where are you expecting an autogrowth to expand to?



So if tempdb now does need to grow for whatever reason, and it causes a SQL issue because it can't, you've guaranteed that there's no easy way to recover, since the current physical files absolutely can't be expanded?


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425207
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse