Is there a way to stop SQL Express 2008 from Idling?

Question

I am using SQL Express 2008 as a backend for a web application, the problem is the web application is used during business hours so sometimes during lunch or break time when there is no users logged in for a 20 minute period SQL express will kick into idle mode and free its cache.

I am aware of this because it logs something like:

Server resumed execution after being idle 9709 seconds

or

Starting up database 'xxxxxxx' in the event log

I would like to avoid this idle behavior. Is there anyway to configure SQL express to stop idling or at least widen the time window to longer than 20mins? Or is my only option to write a service that polls the db every 15mins to keep it spooled up ?

After reading articles like this it doesn't look to promising but maybe there is a hack or registry setting someone knows about.

Pridemore

Answer 1

That behavior is not configurable.

You do have to implement a method to poll the database every so often. Also, like the article you linked to said, set the AUTO CLOSE property to false.

Convex

Does SQL Server 2017 Express edition also idle out after few minutes? – Petrography

Answer 2

Just a short SQL query like this every few minutes will prevent SQLserver from going idle:

SELECT TOP 0 NULL
  FROM [master].[dbo].[MSreplication_options]
GO

Ken

Answer 3

Write a thread that does a simple query every few minutes. Start the thread in your global.asax Application_Start and you should be done!

Dentation

Answer 4

Here is a good explanation: https://blogs.msdn.microsoft.com/sqlexpress/2008/02/22/understanding-sql-express-behavior-idle-time-resource-usage-auto_close-and-user-instances/

Whatever: I do not know the time after sql express goes idle. I suggest to run the script below every 10 minutes (maybe task scheduler). This will prevent SQL Server Express from going idle:

SELECT TOP 0 NULL FROM [master].[dbo].[MSreplication_options] GO

Also make sure all data bases' property is set to AUTO_CLOSE = FALSE

Ken

It is easy to use a batch script for that: \n – Ken