Can't connect to DB when SqlServer Exp has been idle for a couple hours.

_{Thursday, March 13, 2008 4:52 PM}

Does SQLServer go to sleep when it's been idle?  How can I connect without an error?

I have SQLExpress on a server.  Server Name is SqlExpress.  I have 2 DBs on that server... Tuna & Guppy.  I'm connecting to the DBs from a remote PC which runs my App... an exe created with Visual Studio 2008, Visual Basic.

If I run my App at say 9:30PM, after the server has been idle for several hours, I get a "Can't open connection to Guppy" error, which is my error message when the connection attempt fails.  If I quit & relaunch the app, it connects fine, and will continue connecting fine unless you leave the server idle for serveral hours again.

If I'm developing a 2nd app all evening... which connects to a 2nd DB...Tuna, that activity seems to keep the SqlServer awake and then launching the 1st app at 9:30PM connects to Guppy just fine.

I tried adding a "Connection Timeout=30" to my connection string but that did not help.

My connection code is as follows (note the server's name is SQLExpress):

Dim strConnG As String = "Data Source=SQLEXPRESS\SQLEXPRESS;Initial Catalog=Guppy;Integrated Security=True;Connection Timeout=30"

Using ConnG As New SqlConnection(strConnG)
       'open Guppy connection
       Try
           ConnG.Open()
       Catch ex As Exception
            MsgBox("Can't open connection to guppy")
            Return False
       End Try

       'Code that does stuff
End using

Can anyone tell me how to fix this so I can connect without error, even if the server has been idle?

Thank you,

Joe A

_{Friday, March 14, 2008 5:32 PM ✅Answered}

To be specific AutoClose is not the problem here - SQL Express automatically opens any closed databases when a connection is made. The issue was SQL Express "going to sleep" as Joe reasonably figured out. The issue was not that a connection to Guppy failed, it just timed out while SQL Express woke up. If you examine Joe's code, the error about a connection failure is not comming from SQL Express, it's comming from Joe's own code, and in this case was masking the true error of the timeout.

OK, so a timeout is a kind of connection failure, but there are many different reasons a connection can fail other than timeout. Full marks to Joe for having error checking, too many people don't consider that. My recomendation would be to keep the error checking, but durring development, don't eat the SQL error and pass your own, just display the exact SQL error so you know what the specific problem you're facing is. I would write your error trap as:

Code Snippet

Try
    ConnG.Open()
Catch ex As Exception
   MsgBox(ex.Number & ": " & ex.Message)
   Return False
End Try

This way you will see the actual SQL error message. You can go back and write more friendly error messages prior to deployment. Another approach, if you don't want to have to go back and modify your error traps, would be to write a logging routine and write the exact SQL message to the log while printing the friendly message to the user. This way you always have a record of the "real error", even in your deployed application.

Incidently, there is a good demonstration of building logging into your application as part of the Visual Studio "How Do I?" series, check it out at http://msdn2.microsoft.com/en-us/vbasic/cc308392.aspx.

Regards,

Mike

_{Thursday, March 13, 2008 7:35 PM}

By default auto_close may be turned on and that is the problem.  Refer this to check if that is the problem. 

http://blogs.msdn.com/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

and alter database in BOL

_{Friday, March 14, 2008 2:55 AM}

Sankar,

Thank you.  That article was helpful.  I changed my connection string timeout to 60 seconds and at least tonight it did not error on connecting.  I'll watch it and if I have more connection problems I try setting auto_close to false.

Thank you.

Joe A

_{Friday, March 14, 2008 10:00 PM}

Thanks Mike,

My App is deployed, so a friendly message with a log file is a good suggestion.

I made my connection string include a 60 second time out and that worked last night... no error connecting at 9:30 PM when server was idle.  It will take a few more days before I'm sure that was it, but for now I'm hopeful.

My connection string which so far works, is now:

Dim strConnG As String = "Data Source=SQLEXPRESS\SQLEXPRESS;Initial Catalog=Guppy;Integrated Security=True;Connection Timeout=60"

Thanks for the help.

Joe A

_{Saturday, March 15, 2008 1:05 AM}

Joe,

Sorry about leading you towards incorrect problem and Thanks Mike for pointing it out. Appreciate it, next time I will pay more attention before replying. Glad that link was helpful.