Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 733
Element Avatar asked Feb 23 '09 21:02

Element


4 Answers

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.

like image 99
NotMe Avatar answered Oct 19 '22 23:10

NotMe


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
like image 24
Ralph Schwerdt Avatar answered Oct 20 '22 00:10

Ralph Schwerdt


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!

like image 29
Brandon Avatar answered Oct 20 '22 00:10

Brandon


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

like image 24
Ralph Schwerdt Avatar answered Oct 19 '22 23:10

Ralph Schwerdt