SQL EXPRESS TIMEOUT

ECE

The default is sest to 15 seconds that of course is no good because it takes more than that to attact the damm database though is very small.

Does any one know how to modify the connection timeout
I'd try including it on the Connection String but no luck. it looks like it detaches the database after a period of inactivity.

If anyone knows anything about please let me know

THANKS

Hemantgiri S. Goswami

Hi,

Are you attaching your database from QA / Query Window if yes check Tools->Options->Connection to set the time out option Or

run this from query windows and refer this from BOL.

USE master
EXEC sp_configure 'show advanced option', '1'
go
exec sp_configure

Hemantgiri S. Goswami

ECE

Thanks for the info here is the problem

The database is attached by the ASP application I created it is a simple website to submmit information to a database the database in question here is the ASPLOGIN database not where the data goes.
this gets set on the webconfig file on the root site, apparently it attaches e very time the application is first executed I don't use a query to start it because it happens when some one try to LOG IN, the app returns an timeout berror but then your try to log in again and it works fine, this can last for a few hours then after long period of inactivity the same problem occur

The problem only occur at login , once you are log on there is no problem with the application it happens with the SQL EXPRESS and the ASPNET login database

I try to configure as I do with SQL 2000 but the Management tool for SQL EXPRESS is a read only and cant configure the settings

this is the code in web config

I use the wizards to create the grids and forms to display the data but all that info comes from a different database which resides on SQL sever2000 and have no problems with it. again it only occurr with SQL EXPRESS and the ASPNETDB.MDF data base at LOGIN

thanks

Hemantgiri S. Goswami

Hi,

Their are some command you have to specify in your application like command.timeout=someno , here is a wonder full article on Time Out http://vyaskn.tripod.com/watch_your_timeouts.htm , revert back if your SQL Server gives you time out.

Hemantgiri S. Goswami

ECE

Thanks for the reply!

See the problem does not occurr at the ADO nor the queries i run.
It acctually happens before any code I wrote runs, it happens at LOGIN.
I use the Login controll from VisualStudio 2005, this control sets it self and there are no properties as far as connections
The only connection property can ser prior running any code is the one on the Web.config file which have the following string connection:

This is my connection string on the web.config file on my site: prity much standard
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
Adding any Timeout option here dosen't do anything because ASP.net 2.0 ignores it.

As far as I understand when i try to LOGIN on the site, the controll tries to open the ASPNETDB.MDF but SQL Server EXPRESS aparently dettaches it after some period of unactivity so the controll simply TIMES OUT this is because SQL Server Express have to RE ATTACH IT and of course this will take more than 15 seconds to complete... This is a very Idiotic functionality of SQL express far worse than that there is no way to configure the database unlike SQl server 2000 there is the Enterprise manager the one for SQL server Express is a read only and is USELESS.

So the problem as you see is on the Sql server Expresss who after a period of Database inactivity it D-Attaches the ASPNETDB.MDF Database. and when a LOGIN request is received SQL server Express tryies to re attached and Suceeds after 30 seconds, but unfortunately that is too long for the Connection to wait an it craps out..

Any info on how to resolv this problem of hell please let me know

THANKS in Advance

ECE

See the problem does not occurr at the ADO nor the queries i run.
It acctually happens before any code I wrote runs, it happens at LOGIN.
I use the Login controll from VisualStudio 2005, this control sets it self and there are no properties as far as connections
The only connection property can ser prior running any code is the one on the Web.config file which have the following string connection:

This is my connection string on the web.config file on my site: prity much standard
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
Adding any Timeout option here dosen't do anything because ASP.net 2.0 ignores it.

As far as I understand when i try to LOGIN on the site, the controll tries to open the ASPNETDB.MDF but SQL Server EXPRESS aparently dettaches it after some period of unactivity so the controll simply TIMES OUT this is because SQL Server Express have to RE ATTACH IT and of course this will take more than 15 seconds to complete... This is a very Idiotic functionality of SQL express far worse than that there is no way to configure the database unlike SQl server 2000 there is the Enterprise manager the one for SQL server Express is a read only and is USELESS.

So the problem as you see is on the Sql server Expresss who after a period of Database inactivity it D-Attaches the ASPNETDB.MDF Database. and when a LOGIN request is received SQL server Express tryies to re attached and Suceeds after 30 seconds, but unfortunately that is too long for the Connection to wait an it craps out..

Any info on how to resolv this problem of hell please let me know

THANKS in Advance

Mike Wachal - MSFT

You are connecting to a User Instance which is a special type of SQL instance designed for use with single user, local data applications. User Instances are not designed for use with deployed ASP.NET applications. The fact that the VS development tools use this type of connection is not really the fault of SQL Express.

You're actually seeing the combination of two by design behaviors of SQL Express:

All database created in SQL Express have Auto_Close set to True, so the database is closed when there are no connections remaing to the database. When a connection is made, the database is re-opened in an asynchronous operation that is fairly quick and doesn't cause that much of a perf hit. The database is not detached as you've suggested, it is simply closed. Closing the database saves machine resources and typically doesn't impact most single user, Windows applciations.
User Instances are automatically shut down after 60 minutes. Again, this is designed for use as a local data store for windows applications. After you close an application, the User Instance timesout and is shut down. The next time you open the application it will restart the User Instance and connect to the database. This does take some time, but is usually lost in the time taken to start the application itself.

The answer for you is to not use User Instances for your deployed application. You'll need to attach the database directly to the parent instance where ever you are serving your database from and then change your connection string by removing the 'User Instance' keyword and removing the AttachDbFilename keyword and replace it with the name of the database attached to the main server. You may also want to change the Auto_Close setting on the database to ensure it doesn't close, but this behavior may or may not cause you an issue.

The management tools for SQL Express are fully functional and you can use them to configure what ever you want. If you are find the tools as read only, it's is related to your configuration, not because the tools are designed that way. Likely you don't have permissions to make the changes you're attempting. You can get more help with Management Studio Express by posting question directly into the General Tools forum.

Mike

SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT

Re: SQL EXPRESS TIMEOUT