February 28, 2013 at 9:15 am
GilaMonster (2/28/2013)
Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?
No reboot necessary. Not SQL agent though, restart SQL Server itself
Did you mean to say "no reboot necessary" or "no, reboot necessary"?
The reason for the reboot is so the free space will be the same in all files. Technically, it's not necessary, but it's the only way to ensure that the free space is the same and all files get used equally.
February 28, 2013 at 10:28 am
Robert Davis (2/28/2013)
GilaMonster (2/28/2013)
Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?
No reboot necessary. Not SQL agent though, restart SQL Server itself
Did you mean to say "no reboot necessary" or "no, reboot necessary"?
The reason for the reboot is so the free space will be the same in all files. Technically, it's not necessary, but it's the only way to ensure that the free space is the same and all files get used equally.
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.
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
February 28, 2013 at 10:30 am
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 10:37 am
You don't need to correct the situation where one of more of the files has expanded because on restart of SQL the files will be back to their defined size. If both files have a sized defined in the system catalogs of 1GB, then after a restart of SQL they will both be 1GB, regardless of what they automatically grew to before the restart.
Also, if the files are sized the same to start and have the same growth increment, proportional fill will ensure that they fill at the same time and they should all grow at the same time.
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
February 28, 2013 at 10:44 am
GilaMonster (2/28/2013)
You don't need to correct the situation where one of more of the files has expanded because on restart of SQL the files will be back to their defined size. If both files have a sized defined in the system catalogs of 1GB, then after a restart of SQL they will both be 1GB, regardless of what they automatically grew to before the restart.Also, if the files are sized the same to start and have the same growth increment, proportional fill will ensure that they fill at the same time and they should all grow at the same time.
So proportional fill will "ensure" that? I wasn't sure that was guaranteed. I know it wasn't in the past.
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 10:46 am
GilaMonster (2/28/2013)
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.
February 28, 2013 at 10:48 am
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.
February 28, 2013 at 10:48 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2013 at 10:49 am
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.
February 28, 2013 at 10:51 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 10:52 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 10:53 am
ScottPletcher (2/28/2013)
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.
February 28, 2013 at 10:56 am
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?
February 28, 2013 at 11:02 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2013 at 11:04 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply