From: salsafreak on
My environment is: SQL 2005 64-bit two-node Cluster operating in a 64-bit
environment (Windows Server 2003 64-bit SP2) on an iSCSI SAN. We have been
successfully running for a long time (over a year) with a striped tempdb (4
physical files - 4 physical CPUs). The stripes were created as 4 12288 GB
files when we initially configured the environment - according to tempdb
configuration best practices. Yesterday we experienced a cluster failover
and when SQL Server restarted it recreated tempdb as a single tiny (original
configuration?) file that is barely 300MB in size (it has already autogrown).
Interestingly, the log file was attached with the last size it had (ie, it
didn't get reconfigured).

My questions (and I post this here in the General SQL Server section since
this may not necessarily be related to Clustering):

What would cause tempdb to revert back to its original (as in, SQL Server
out of the box just got installed) size? How can I keep this from happening
again (assuming I can)?

I believe the system keeps the info on the specs (4 stripes, sizes, etc.) in
master.sys.master_files but would like some confirmation on this since I'd
like to confirm that, once I fix it, this will not happen again.

TIA... Raul