ScottPletcher (2/27/2013)
GilaMonster (2/27/2013)
ScottPletcher (2/27/2013)
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.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)
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?
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.
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