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.