Robert Davis (2/27/2013)
ScottPletcher (2/27/2013)
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!]
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.
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.