I have a large database which is consistently taking between 30 and 60 minutes to come up after restarting SQL Server. How can I speed this up? Please read below before responding.
I have a Winows 2003, 2 node cluster running 1 instance of SQL Server 2005 enterprise ed. The server has 64GB of RAM, and 4 Quad core chips (16 logical processors). The storage is on SAN, and is a mix of RAID 10 and RAID 5 volumes.
The instance has multiple DB's in it but 1 is far and away the largest @ 2TB allocated (about 1TB actual data). The database in question is broken into many datafiles as follows:
Primary filegroup = 1 10GB datafile
Secondary filegroup = 8 x 100GB datafiles spread evenly over 4 seperate volumes for I/O performance.
Partitioning filegroups = many many files used for a partitioned table (file sizes ranging from 1.5GB to 20GB)
Again, my issue is the following. This 2TB database takes close to an hour to startup everytime the instance of SQL Server restarts, failsover etc...
Couple of notes:
1) We have instant file initialization configured
2) Recovery model = simple
3) Recovery interval = 0
4) There are no active large transactions running when the instance goes down
View Complete Post