March 4, 2013 at 10:12 am
Robert Davis (3/4/2013)
ScottPletcher (2/28/2013)
Yes, that's 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 absolutely never 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"?]
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?
"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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2013 at 10:19 am
GilaMonster (3/4/2013)
ScottPletcher (3/4/2013)
GilaMonster (2/28/2013)
stehoban (2/27/2013)
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 ?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.
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
For SQL 2008, that works great.
For SQL 2005, as this forum is, you can't do that.
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)
I've never had an issue shrinking log files at start up.
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.
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.
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)
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.
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.
"I've never had an issue shrinking log files at start up."
Typo: should have been data files.
The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.
Yes, if 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:
How to i reduce the size from 1.5gb to 800mb or 1gb?
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 4, 2013 at 3:36 pm
ScottPletcher (3/4/2013)
The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.
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.
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
March 4, 2013 at 4:04 pm
GilaMonster (3/4/2013)
ScottPletcher (3/4/2013)
The ALTER command to try to change an existing file to a smaller size gives you an error on SQL 2005.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.
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."
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 46 through 48 (of 48 total)
You must be logged in to reply to this topic. Login to reply