Sql server fall asleep after a while idle


  • Hi, I am running SQL server 2008 R2 Developer edition and when I moved my app from localhost to actual server a weird error. When my application doesn't make any sql request for some longer time, the sql server appears to be "sleeping". If I want to  open a connection after a while, every first request ends with exception. If i let the sql server about a minute to "wake up", everything works fine and all following request works like a charm. Any ideas what could be wrong? 


    Tuesday, June 25, 2013 6:19 PM


  • Could be Windows has paged out the process because it is idle. You can configure a minimum memory setting by using Server Memory Server Configuration Options

    Could be you are connecting to a database with the AUTO_CLOSE option set to ON. "The database is shut down cleanly and its resources are freed after the last
    user exits. The database automatically reopens when a user tries to use the database again." See the ALTER DATABASE topic.  

    You might be able to resolve it by increasing the connection timeout value.

    Also look in the Windows Event logs and SQL Server Error logs for indications of other issues. There could be network things going on. Delays caused by stale DNS cache, or delays reaching the domain controller for login validation.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, June 26, 2013 3:51 PM