ADODB Connection Open: Intermittent Timeout Expired Errors

Hello all-
Connecting to a SQL Server 2005 Express w/Advanced Services database using
ADODB in vbscript. The database is on the local server and am using shared
memory. The server has dual processors and 3GB Ram, but SQL Server Express is
limited to 1 processor and 1 GB of ram.
Every once in a great while I receive timeout expired errors while trying to
connect to the database. When it works, it connects in less than a
millisecond. Therefore I believe that increasing the timeout period in the
connection string rather than using its default of 30 seconds is not going to
fix this problem.
Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
Catalog=Ingen;Integrated Security=SSPI;"
Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
Call dbConnection.Open(CONNECTION_STRING)
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
Any suggestions?
Thank you in advance.
Go ahead and try the longer timeout. There may be other things happening on
your system that occasionally cause a hesitation. One of the more common
issues is that SQL Express databases have the autoclose option set by
default. This means that when no users are logged into a database, SQL
Server shuts it down to safe resources. The first user to login after the
database shuts down waits for the database to start up before the login is
complete so that may be what is causing the timeout. Generally starting up
a database takes only a few seconds but if the system is busy doing other
things it may take a while for SQL Server to obtain the resources it needs.
You can check this by using ALTER DATABASE to turn off the autoclose option
on your main databases.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:89E366F9-6946-4B45-86AD-CC7B9B0EB5BE@.microsoft.com...
> Hello all-
> Connecting to a SQL Server 2005 Express w/Advanced Services database using
> ADODB in vbscript. The database is on the local server and am using
> shared
> memory. The server has dual processors and 3GB Ram, but SQL Server Express
> is
> limited to 1 processor and 1 GB of ram.
> Every once in a great while I receive timeout expired errors while trying
> to
> connect to the database. When it works, it connects in less than a
> millisecond. Therefore I believe that increasing the timeout period in the
> connection string rather than using its default of 30 seconds is not going
> to
> fix this problem.
> Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
> Catalog=Ingen;Integrated Security=SSPI;"
> Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")
> Call dbConnection.Open(CONNECTION_STRING)
> Microsoft OLE DB Provider for SQL Server error '80040e31'
> Timeout expired
> Any suggestions?
> Thank you in advance.
|||Thanks Roger for your response. We started getting this error after
upgrading from MSDE to the 2005 Express version. Is this auto-close
something new to 2005? I verified that I do have auto-close turned off for my
database properties.
What would you suggest increasing the timeout period to?
|||Yes, also look in the SQL Server errorlog file to see if any errors are
logged when the timeout happens.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:D29FD261-9A6C-4083-8D1E-22C1914DB2DC@.microsoft.com...
> Thanks Roger for your response. We started getting this error after
> upgrading from MSDE to the 2005 Express version. Is this auto-close
> something new to 2005? I verified that I do have auto-close turned off for
> my
> database properties.
> What would you suggest increasing the timeout period to?
|||Where do I find the SQL Server errorlog file?
I am on SQL Server Express so it will not let me view the logs in the SSMSE
interface. If you are talking about the logs in the Event Viewer, there was
nothing logged.
Thanks
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.
|||Where do I find the SQL Server errorlog?
I am on SQL Server Express and cannot view the transaction logs from within
SSMSE. If you are talking about the system logs in the Event Viewer, there
was nothing logged in here during the time frame of the connection error.
"Roger Wolter[MSFT]" wrote:

> Yes, also look in the SQL Server errorlog file to see if any errors are
> logged when the timeout happens.
|||C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
assuming you installed express first and to the default location.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...[vbcol=seagreen]
> Where do I find the SQL Server errorlog?
> I am on SQL Server Express and cannot view the transaction logs from
> within
> SSMSE. If you are talking about the system logs in the Event Viewer,
> there
> was nothing logged in here during the time frame of the connection error.
> "Roger Wolter[MSFT]" wrote:
|||Roger-
Increasing the timeout in the connection string appears to have fixed the
problem! No errors since making the change, so I think that's a good sign.
Thanks for your help.
"Roger Wolter[MSFT]" wrote:

> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> assuming you installed express first and to the default location.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Brian Kudera" <BrianKudera@.discussions.microsoft.com> wrote in message
> news:17F23E00-0514-43F1-9822-88F61816AD2D@.microsoft.com...
>
>