Menu
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?
331
>> By using In-Memory tables (memory-optimized table) and columnstore indexes in Azure SQL Database, you can achieve performance improvements and the data will be loaded to memory. >> Simply execute your most common queries/stored procedure to warm up the relevant data and to create the execution plan in advance.
However, closing user sessions and query windows can reduce the number of sleeping sessions by around 20%.
MS SQL data warehousing server processes all share the same pool of compute resources. Snowflake allows you to segregate use cases into their own compute buckets, improving performance and managing cost. Additionally, sometimes you need to throw a lot of computing power at a specific data-processing need.
If your database is stuck in EMERGENCY mode, try to look for the last backup to restore the database and recover its information. If the backup is corrupt or unavailable, use a SQL recovery tool like Stellar Repair for MS SQL to restore your database to its original state.
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.
86
ALTER DATABASE myDB SET AUTO_CLOSE OFF
go
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
