How to warm up a sleeping SQL Server database

Question

We have a job which runs every morning in CruiseControl.net to import a datafile into a DB. Our problem seems to be that some nights the DB goes to sleep over night. When we run the script in the morning our connection times out while we wait for the DB to warm up. We've tried upping timeouts etc. but with no luck. Is there a way to tell SQL Server to warm up at a set time or to tickle it to keep it warm?

So far we run the job twice, once at 9am to warm it up which fails 80% of the time but at least it warms the server up and again an hour later which nearly always works.

I'd rather not run it twice as its a big job and very CPU intensive. Any ideas?

Answer 1

Could you schedule a query 30 minutes before your build process runs to "warm up" the server?

Also- is this database running on a server or desktop? If it's a desktop you might check the power management settings.

Answer 2

ALTER DATABASE myDB SET AUTO_CLOSE OFF
go

Answer 3

Which version of SQL Server is this? It sounds like the database has the Auto Close setting on, which is the default for EXPRESS but can lead to behaviour like this.