InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:05:40 GMT20http://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (3/4/2013)[/b][hr][quote][b]ScottPletcher (3/4/2013)[/b][hr]The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.[/quote]That's when you're trying to reduce the initial size, so when the DB's size has been defined via alter database at 1.5 GB, then you cannot shrink it down to 1 GB. That requires DBCC ShrinkFile. The case in the original post of this question was when the DB has grown too large, via autogrow, the initial size is defined in the system catalogs as 8 MB and you want to make the file 1 GB in size. With that, you can definitely use Alter Database, with TempDB you might (and I repeat might) need to restart first and then specify the larger file size. Just one restart though, not multiple. The restart puts TempDB back to the default size, then you specify the ALTER with the larger file size.If you have a TempDB that someone has explicitly specified the size too large and you want to change the initial defined size that's in the system catalogs to a lower size (so someone mistakenly defined TempDB as 10GB not 1 GB), that's the time you need a DBCC ShrinkFile and for TempDB that should be done with the server in single user mode. That said, unless you have people often altering TempDB and specifying the wrong size, that certainly shouldn't be something done often, if it ever needs doing at all.[/quote]You live in a placid and sheltered environment ... what a joy it must be!As we consolidate SQL instances, inevitably there are laggards who can't or won't move their stuff quickly enough to the more-shared instances. However, once most dbs and other things are moved, the instance doesn't need the same disk allocations for tempdb it had when it was fully loaded. Thus the need to shrink existing tempdbs. "There are more things in heaven and earth ... Than are dreamt of in your philosophy."Mon, 04 Mar 2013 16:04:33 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (3/4/2013)[/b][hr]The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.[/quote]That's when you're trying to reduce the initial size, so when the DB's size has been defined via alter database at 1.5 GB, then you cannot shrink it down to 1 GB. That requires DBCC ShrinkFile. The case in the original post of this question was when the DB has grown too large, via autogrow, the initial size is defined in the system catalogs as 8 MB and you want to make the file 1 GB in size. With that, you can definitely use Alter Database, with TempDB you might (and I repeat might) need to restart first and then specify the larger file size. Just one restart though, not multiple. The restart puts TempDB back to the default size, then you specify the ALTER with the larger file size.If you have a TempDB that someone has explicitly specified the size too large and you want to change the initial defined size that's in the system catalogs to a lower size (so someone mistakenly defined TempDB as 10GB not 1 GB), that's the time you need a DBCC ShrinkFile and for TempDB that should be done with the server in single user mode. That said, unless you have people often altering TempDB and specifying the wrong size, that certainly shouldn't be something done often, if it ever needs doing at all.Mon, 04 Mar 2013 15:36:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (3/4/2013)[/b][hr][quote][b]ScottPletcher (3/4/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]stehoban (2/27/2013)[/b][hr]Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?[/quote]Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itself[/quote]For SQL 2008, that works great.For SQL 2005, as this forum is, you can't do that.[/quote]SQ 2000, 2005, 2008, 2008 R2, 2012. Probably 7, but I never worked with it.At restart TempDB is reset back to the size defined in the system catalog and cleared. I've depended on that behaviour in SQL 2000 (several years ago) and SQL 2005 (just last month)[quote]I've never had an issue shrinking log files at start up.[/quote]No one said anything about the log. It's the data files that are the ones that could get corrupted if shrinking TempDB. There's a KB article that describes the potential problems and discusses the ways to shrink TempDB, mostly being restart SQL and let it go back to default. Want to shrink below default, start SQL in single user mode and then shrink.[quote]And I wouldn't expect a 1.5G tempdb, as in the case we're discussing, to be so used it couldn't be shrunk to 1GB at start up.[/quote]How heavily used TempDB is before the restart is completely irrelevant, since it's cleared upon restart. So unless you have TempDB defined at 1 GB and model with 1.5 GB of stuff in, there's no problems (and if you do, there are lots of other potential problems)[quote]If you're in the ideal situation of being able to be present for every server as it comes up, with the time available and allowed to restart as needed, etc., then, yeah, you can do everything strictly by the book.[/quote]I used to work at an investment bank, the servers restarted at most once a month (patches), usually somewhere around 1AM on a sunday morning. I certainly wouldn't plan on being present when the restart happens, if I even knew about it before hand. Restarting as needed, not a chance in hell, took 5 documents and a week's notice to schedule a reboot.I don't know why you're so fixated on shrinking TempDB anyway, if it's regularly growing, it needs a larger default size (and better monitoring so that it can be manually grown before an autogrow would be necessary). Only time when it's grown and you don't want a larger default size is if something unusual happened (runaway query, data import, etc). In that case, just check all files are still the same size, grow any that aren't and let the next restart (whenever it is) handle getting the files back to default size.[/quote]"I've never had an issue shrinking log files at start up."Typo: should have been [i]data[/i] files.The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.Yes, [i]if[/i] the pure method worked in SQL 2005, as it does in SQL 2008, that would be great, but it doesn't (at least for SP2 and SP3, don't have other easily available right now to confirm)."I don't know why you're so fixated on shrinking TempDB anyway"Because that was one of the key qs here:[quote]How to i reduce the size from 1.5gb to 800mb or 1gb?[/quote]and I've had to do it many times on many servers. I don't have time to do them all by hand, and there's not a DBA on site when it's done, so it needs to be automatic.Mon, 04 Mar 2013 10:19:26 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (3/4/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]Yes, [i]that's[/i] the problem. You're scattering (small) I/Os all over the drive, causing lots of drive head movement, which is brutal on performance.Allocating "one drive" is very nebulous. Does this mean 2TB? 3TB? 5TB? No matter what SQL actually needs. How do you know ahead of time for a new instance what a massively-oversize, will [b]absolutely never[/b] fill up drive size is? [Btw, how do you get the storage people to let you waste TBs of unused disk space on half-filled "tempdb drives"?][/quote]We weren't talking size. We were talking whether or not additional files should be separated to a different drive. How is saying "on a single drive" nebulous?There is no way to know what size could never possibly be filled up. That's true whether you pre-size the files out or not. Whether you pre-size or not, it doesn't change how you estimate how big the drive should be. You're really grasping at straws in an effort to continue arguing.I don't waste TBs of space. You pulled that out of your backside, I never advocated having multi-TB drives for tempdb. You're so intent on arguing that you're making stuff up now. I aslo don't leave tempdb drives half-filled. I have stated many times in this thread that I pre-size the files out to consume at least 90% of the drive space. In what world is at least 90% allocated the same as half-filled?[/quote]"How is saying "on a single drive" nebulous?":All drives aren't a fixed size; saying you allocate "a whole drive" to tempdb is nebulous as to the amount of space."I don't waste TBs of space."This person needs 1.5G of space for tempdb. What's the smallest drive size you can realistically buy now? Add a few of those together and you're easily wasting TBs of space."In what world is at least 90% allocated the same as half-filled?"Allocated is not the same as used. In this case, a, say, 400GB allocation would be 99% unfilled/unused.Mon, 04 Mar 2013 10:12:49 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (3/4/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]stehoban (2/27/2013)[/b][hr]Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?[/quote]Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itself[/quote]For SQL 2008, that works great.For SQL 2005, as this forum is, you can't do that.[/quote]SQ 2000, 2005, 2008, 2008 R2, 2012. Probably 7, but I never worked with it.At restart TempDB is reset back to the size defined in the system catalog and cleared. I've depended on that behaviour in SQL 2000 (several years ago) and SQL 2005 (just last month)[quote]I've never had an issue shrinking log files at start up.[/quote]No one said anything about the log. It's the data files that are the ones that could get corrupted if shrinking TempDB. There's a KB article that describes the potential problems and discusses the ways to shrink TempDB, mostly being restart SQL and let it go back to default. Want to shrink below default, start SQL in single user mode and then shrink.[quote]And I wouldn't expect a 1.5G tempdb, as in the case we're discussing, to be so used it couldn't be shrunk to 1GB at start up.[/quote]How heavily used TempDB is before the restart is completely irrelevant, since it's cleared upon restart. So unless you have TempDB defined at 1 GB and model with 1.5 GB of stuff in, there's no problems (and if you do, there are lots of other potential problems)[quote]If you're in the ideal situation of being able to be present for every server as it comes up, with the time available and allowed to restart as needed, etc., then, yeah, you can do everything strictly by the book.[/quote]I used to work at an investment bank, the servers restarted at most once a month (patches), usually somewhere around 1AM on a sunday morning. I certainly wouldn't plan on being present when the restart happens, if I even knew about it before hand. Restarting as needed, not a chance in hell, took 5 documents and a week's notice to schedule a reboot.I don't know why you're so fixated on shrinking TempDB anyway, if it's regularly growing, it needs a larger default size (and better monitoring so that it can be manually grown before an autogrow would be necessary). Only time when it's grown and you don't want a larger default size is if something unusual happened (runaway query, data import, etc). In that case, just check all files are still the same size, grow any that aren't and let the next restart (whenever it is) handle getting the files back to default size.Mon, 04 Mar 2013 10:10:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]Yes, [i]that's[/i] the problem. You're scattering (small) I/Os all over the drive, causing lots of drive head movement, which is brutal on performance.Allocating "one drive" is very nebulous. Does this mean 2TB? 3TB? 5TB? No matter what SQL actually needs. How do you know ahead of time for a new instance what a massively-oversize, will [b]absolutely never[/b] fill up drive size is? [Btw, how do you get the storage people to let you waste TBs of unused disk space on half-filled "tempdb drives"?][/quote]We weren't talking size. We were talking whether or not additional files should be separated to a different drive. How is saying "on a single drive" nebulous?There is no way to know what size could never possibly be filled up. That's true whether you pre-size the files out or not. Whether you pre-size or not, it doesn't change how you estimate how big the drive should be. You're really grasping at straws in an effort to continue arguing.I don't waste TBs of space. You pulled that out of your backside, I never advocated having multi-TB drives for tempdb. You're so intent on arguing that you're making stuff up now. I aslo don't leave tempdb drives half-filled. I have stated many times in this thread that I pre-size the files out to consume at least 90% of the drive space. In what world is at least 90% allocated the same as half-filled?Mon, 04 Mar 2013 09:49:11 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]stehoban (2/27/2013)[/b][hr]Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?[/quote]Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itself[/quote]For SQL 2008, that works great.For SQL 2005, as this forum is, you can't do that.I need the changes to take automatically take effect on the next start of SQL 2005. I can't be available to do multiple stops and starts of SQL, and many (paper) mills wouldn't allow that to occur anyway. Some equipment is multi-multi-million dollar equipment and [b]never[/b] stops except for schedule machine maintenance. It's nice when they can afford the time to reboot/restart SQL even once while up.I don't know of any other automatic SQL 2005 method than the one I've outlined here. I've never had an issue shrinking log files [i]at start up[/i]. And I wouldn't expect a 1.5G tempdb, as in the case we're discussing, to be so used it couldn't be shrunk to 1GB at start up.If you're in the ideal situation of being able to be present for every server as it comes up, with the time available and allowed to restart as needed, etc., then, yeah, you can do everything strictly by the book.But for someone without SQL expertise, I've found the automatic method works better. My experience has been that even Windows admins sometimes aren't ideal for starting SQL in special mode, issuing unfamiliar SQL-based commands, etc..Mon, 04 Mar 2013 09:27:59 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxInt'l Paper also was buying lots of companies for awhile, some of them other paper companies.During a merger, there are legal restrictions on what can and can't be viewed, in stages of nn-days before the official merger date (and other legal milestone dates as well).Sometimes we kept old support personnel from the original company, other times not.For one acquisition, one day I simply had 12 more prod SQL instances to support, which I had not been logged into before. Those kind of situations force you to develop more flexibility in how you handle certain things.I guess I should wish I had the luxury of the ultra-controlled environments some people have, but it's a mixed blessing/curse. I know several people at FedEx and they are in [b]NO-ERROR PERIOD[/b] production envs. But it takes them 3 days to change a title on a screen because of all the code and app checks everything must go thru.Thu, 28 Feb 2013 12:48:14 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr]Exactly. If the drive space is there, why not allocate it? I'm not going to use it for anything else.The reason for allocating multiple files is to avoid tempdb contention, and overloading the drive with IO is rarely a concern. On those rare occasions, where IO becomes a problem on the drive, I will separate the files to multiple drives. simply writing the same amount of IO to more files does not greatly increase the amount of IO going to the drive. There is some increase because more files affect the size of I/O block you can write. If writing to one file, it will write a larger block (when it's writing enough to need to write the larger block) than when to multiple files. That IO block size decreases a little each time you add another file, but it flattens out at 8 files. So the IO block size will be the same for 8 data files as it is for 16 files or 24 files or more.The SQLCat team measured this in benchmarking tests (specifically driving a workload that would take advantage of large writes):1 file = 256 KB max IO block size2 files = 196 KB max IO block size4 files = 132 KB max IO block size8 files = 64 KB max IO block size16 files = 64 KB max IO block size32 files = 64 KB max IO block size[/quote][i]That IO block size decreases a little each time you add another file[/i]Yes, [i]that's[/i] the problem. You're scattering (small) I/Os all over the drive, causing lots of drive head movement, which is brutal on performance.Allocating "one drive" is very nebulous. Does this mean 2TB? 3TB? 5TB? No matter what SQL actually needs. How do you know ahead of time for a new instance what a massively-oversize, will [b]absolutely never[/b] fill up drive size is? [Btw, how do you get the storage people to let you waste TBs of unused disk space on half-filled "tempdb drives"?]Thu, 28 Feb 2013 12:42:01 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxExactly. If the drive space is there, why not allocate it? I'm not going to use it for anything else.The reason for allocating multiple files is to avoid tempdb contention, and overloading the drive with IO is rarely a concern. On those rare occasions, where IO becomes a problem on the drive, I will separate the files to multiple drives. simply writing the same amount of IO to more files does not greatly increase the amount of IO going to the drive. There is some increase because more files affect the size of I/O block you can write. If writing to one file, it will write a larger block (when it's writing enough to need to write the larger block) than when to multiple files. That IO block size decreases a little each time you add another file, but it flattens out at 8 files. So the IO block size will be the same for 8 data files as it is for 16 files or 24 files or more.The SQLCat team measured this in benchmarking tests (specifically driving a workload that would take advantage of large writes):1 file = 256 KB max IO block size2 files = 196 KB max IO block size4 files = 132 KB max IO block size8 files = 64 KB max IO block size16 files = 64 KB max IO block size32 files = 64 KB max IO block sizeThu, 28 Feb 2013 11:54:23 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]I have additional space to grow, you prevent it.So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.[/quote]Umm, no. My file size is already much larger than yours, so if a process needs say 50MB more space, yours has to stall and perform an auto-grow while my continues onward happily because my file is already large enough.If on the other hand, the file has already autogrown to fill the entire drive (and mine is already pre-sized to that size) then where are you getting this additional space to grow like you say above? Please educate me on these magical tempdb drive fairies that give you additional space even when the drive is full.[/quote]So you massively overallocate tempdb. That's one approach I guess, if you're not required to justify drive costs.Why do you insist on having all tempdb files on [b]one[/b] drive? Given how proportional fill works, aren't you banging the bejeebers out of that [b]one[/b] drive?Thu, 28 Feb 2013 11:41:31 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]I have additional space to grow, you prevent it.So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.[/quote]Umm, no. My file size is already much larger than yours, so if a process needs say 50MB more space, yours has to stall and perform an auto-grow while my continues onward happily because my file is already large enough.If on the other hand, the file has already autogrown to fill the entire drive (and mine is already pre-sized to that size) then where are you getting this additional space to grow like you say above? Please educate me on these magical tempdb drive fairies that give you additional space even when the drive is full.Thu, 28 Feb 2013 11:37:38 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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? [/quote]Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.[/quote]I don't miss the signs of it.I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.You just have to have someone [i]manually[/i] intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.[/quote]One big difference. If I'm paying attention the autogrow never happens. I'll have seen the increased usage of TempDB and manually grown the files (at a quiet time) before the autogrow would be required.TempDB autogrowing is a sign that I've stuffed up, not done my job properly.[/quote]Yes, you're clearly in the preferred position, where you have massive time to devote to every server.And you [b]never[/b] have developer(s) accidentally overfill a temp table(s).Have to admit, International Paper wasn't that organized, and with dozens of servers spread across the country, and hundreds of developers -- who were not fully monitored all the time -- we had things "come up" with tempdb.Hmm, so do you too, then, disable autogrow on your temp tables? That's an interesting approach, although I admit I'm not ready to try it on my company's system.Thu, 28 Feb 2013 11:37:09 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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?[/quote]A) How does this differ from tempdb trying to autogrow past the size of the drive and failing because you've filled up the drive?B) Yes, there's an easy way to recover. The transaction returns an error and rolls back. Which is exactly the same thing that would happen if you auto-grew the file to use the whole drive and it ran out of space, except in my case, it would happen quicker because it wouldn't have to attempt the auto-growth before failing.What are you expecting to happen? Really, your arguments are not logical.[/quote]I have additional space to grow, you prevent it.So if a task unexpectedly needs, say, 50MB more of tempdb space to complete, you abend it -- perhaps after it ran for hours, so you can look forward to twice that long in rollback time -- while I let it complete.It's 100% illogical to assume that ANY expansion of tempdb will result in a runaway condition that will fill all tempdb drives.[Yes, I had to manage dozens of servers spread across the country, and I didn't get to do daily watch over all of them all of the time, so I made the "second/third" tier servers as self-correcting as possible. But the above situation could apply to any server any time.]Thu, 28 Feb 2013 11:30:33 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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? [/quote]Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.[/quote]I don't miss the signs of it.I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.You just have to have someone [i]manually[/i] intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.[/quote]One big difference. If I'm paying attention the autogrow never happens. I'll have seen the increased usage of TempDB and manually grown the files (at a quiet time) before the autogrow would be required.TempDB autogrowing is a sign that I've stuffed up, not done my job properly.Thu, 28 Feb 2013 11:28:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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? [/quote]Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.[/quote]I don't miss the signs of it.I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.You just have to have someone [i]manually[/i] intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.Thu, 28 Feb 2013 11:21:09 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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?[/quote]A) How does this differ from tempdb trying to autogrow past the size of the drive and failing because you've filled up the drive?B) Yes, there's an easy way to recover. The transaction returns an error and rolls back. Which is exactly the same thing that would happen if you auto-grew the file to use the whole drive and it ran out of space, except in my case, it would happen quicker because it wouldn't have to attempt the auto-growth before failing.What are you expecting to happen? Really, your arguments are not logical.Thu, 28 Feb 2013 11:12:20 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxIf you were successfully sizing tempdb to be the size that SQL needs, you wouldn't be using startup procs to check file sizes and be concerned about checking the files to see if they grew any and resizing the files if it happened.That's okay. You keep doing it the hard way and making tempdb take stalls for auto-growth events. I'm going to do it the smart way and ensure that tempdb doesn't have to auto-grow. Less work for me and better performance for my tempdb.Thu, 28 Feb 2013 11:08:02 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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? [/quote]Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.Thu, 28 Feb 2013 11:06:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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?[/quote]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?Thu, 28 Feb 2013 11:04:25 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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?[/quote]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..Thu, 28 Feb 2013 11:02:33 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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?Thu, 28 Feb 2013 10:56:10 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr][quote]So proportional fill will "[b]ensure[/b]" that? I wasn't sure that was [b]guaranteed[/b]. I know it wasn't in the past.[/quote]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.Thu, 28 Feb 2013 10:53:43 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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.Thu, 28 Feb 2013 10:52:23 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.[/quote]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.Thu, 28 Feb 2013 10:51:15 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.Thu, 28 Feb 2013 10:49:55 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]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.Thu, 28 Feb 2013 10:48:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/28/2013)[/b][hr]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.[/quote]Read my whitepaper linked above and you'll have your answer, or at least my version of it.Thu, 28 Feb 2013 10:48:16 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][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.[/quote]Cool. then I agree completely.Thu, 28 Feb 2013 10:46:10 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr]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.[/quote]So proportional fill will "[b]ensure[/b]" that? I wasn't sure that was [b]guaranteed[/b]. 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.Thu, 28 Feb 2013 10:44:01 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxYou 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.Thu, 28 Feb 2013 10:37:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxSo 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.Thu, 28 Feb 2013 10:30:02 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/28/2013)[/b][hr][quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b][quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itself[/quote]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.[/quote]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.Thu, 28 Feb 2013 10:28:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]GilaMonster (2/28/2013)[/b][hr][quote][b][quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itself[/quote]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.Thu, 28 Feb 2013 09:15:32 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]stehoban (2/27/2013)[/b][hr]Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?[/quote]Not shrink. Alter database and set the initial size of the original TempDB file. That size takes effect on a restart of SQL, resulting in 2 files of the defined size.[quote]Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?[/quote]No reboot necessary. Not SQL agent though, restart SQL Server itselfThu, 28 Feb 2013 01:52:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/27/2013)[/b][hr][quote][b]GilaMonster (2/27/2013)[/b][hr][quote][b]ScottPletcher (2/27/2013)[/b][hr]Then, set up a start up proc to shrink both tempdb files ... I'd say to 1G each, so you have some cushion. You really don't ever want the tempdb data files dynamically growing if you can avoid it.[/quote]Why would you set a startup proc to shrink TempDB when restarting SQL sets TempDB back to it's defined size? If the files are wanted at 1GB each, set the size of both to 1GB and when SQL starts up the files will be 1 GB each without any need for shrinks (which is documented to be able to cause corruption in TempDB)[/quote]If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [/quote]No, it's set to it's default size. So if the file's initial size is 800MB and it's grown to 1.5 GB, on restart it'll be 800MB again. That's the only safe way to shrink TempDB files (other than starting SQL in single user mode and running a shrinkfile). Any form of shrink where there's a chance for other users (even at the point of startup) can cause corruption that requires a restart to fix.Thu, 28 Feb 2013 01:46:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxYes, that was the reason for his suggestion of two files at 800MB so that together they would be about 1.5 GB. Personally, I always advocate pre-sizing your tempdb files to consume ~90% of the available drive space.You can read my recommendations in full detail in the white paper I wrote on tempdb: [url=https://www.idera.com/Action/RegisterWP.aspx?WPID=43][/url]Wed, 27 Feb 2013 21:15:19 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspxOk thanks for the info.So just to be clear about this one. 1. I can put the secondary tempdb file on the same disc as the primary file.2. Make the second file an inital size of say 1Gb3. Reduce the current size of the primary tempdb to 1 Gb (or whatever size i have set in point 2 above)4. Reboot the server for the change in the primary file to take effect.Regarding point number 2 above. My current size is 1.5gb, so would it not be better to make the second file 1.5gb or is the reason for your suggestion of 800mb because there would be 2 files of 800mb each therefore equating to approx 1.5gb in total ? Regarding point number 3 above. How to i reduce the size from 1.5gb to 800mb or 1gb ? Would this be by using the ALTER database command with the shrinkfile statement ?Regarding point number 4 above. Is a reboot necessary - could i just restart the sql server agent ?ThanksStevenWed, 27 Feb 2013 16:20:09 GMTPearlJammer1http://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]Robert Davis (2/27/2013)[/b][hr][quote][b]ScottPletcher (2/27/2013)[/b][hr]If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!][/quote]No, it returns to the defined size, not the physical size. The defined size for tempdb files are maintained in master.sys.master_files whereas the current size is maintained in tempdb.sys.database_files. These two tables are the same for all databases except tempdb. Only tempdb is tracked differently like this.[/quote]OK. I always explicitly specify the size of tempdb data files, and they don't grow, so mine come back up the same size.I have had to used start up procs in the past, but hopefuly that's obsolete now, if you can issue an ALTER on the size and have the file automatically become that smaller size when SQL restarts.Wed, 27 Feb 2013 15:09:26 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1424621-146-1.aspx[quote][b]ScottPletcher (2/27/2013)[/b][hr]If a tempdb data file is 1.5G when SQL goes down, won't it be 1.5G when SQL comes back up? Isn't it automatically reset to its previous size? [And from a performance perspective, that's what you want it to do!][/quote]No, it returns to the defined size, not the physical size. The defined size for tempdb files are maintained in master.sys.master_files whereas the current size is maintained in tempdb.sys.database_files. These two tables are the same for all databases except tempdb. Only tempdb is tracked differently like this.Wed, 27 Feb 2013 14:58:45 GMTRobert Davis