Slow Startup from Idle

Could you describe your problem it a bit more detail? Are you connecting to the express instance using "user instance=true" in the connection string?

Do logins seem to take longer if the instance is idle some particular amount of time?

Hi Bob,

That's actually the expected behavior of User Instances. Check out the User Instance white paper for more details. In short, a user instance is a separate instance of SQL Express that is started up at runtime. As long as an application is connected to the User Instance, it runs as any other SQL service would. Once all connections have been shut down, the instance will hang around for a short time (about 10 minutes) and then shut down.

It sounds like you probably don't want User Instances, but I'll let you know ahead of time, that this will change some things about your application. If you stop using user instances, you may not want to use AttachDbFilename either, rather you'll want to attach the database to you main instance (probably <machine name>\SQLEXPRESS and then modify your connection string appropriatly to point at the database rather than attach it dynamically. Depending on what exactly you're doing you may need to modify or add some Login and User information to your server as well. User Instances have a special quality of running as the logged in user and giving them certain permissions, so they can access the database. When you move to the main instance, you have to setup security on your server.

Regards,

Mike Wachal
SQL Express team

Hello,
I'm also experiencing this same problem, with a number of servers. It doesn't sleep that often, but when it does it takes a long time to load up. I get messages like the following:
Server resumed execution after being idle 1360 seconds: user activity awakened the server. This is an informational message only. No user action is required.
I think the server may have been installed with "Enable User Instances" during setup.
The database in question was either detached/reattached or backed-up/restored from another server (not sure if this would make any difference)
Will User Instances need to be disabled at a server level? If so, how is that done? How am I able to tell whether user instances is enabled or disabled at the server level?
If anyone could help that'd be great.
Thanks,
Antonio

I don't think people are clear on the difference between User Instances and the main instance of SQL Express.

You will only get a User Instance if you are connecting to SQL Express using a specialized connection string that specifies two things:

1. That a User Instance should be used.
2. The path to the database file that should be attached when the application starts.

The connection string would look something like this:

Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|\myDatabase.mdf;User Instance=True

This connection string will cause an initial connection to the main instance (.\SQLEXPRESS) and then instruct the main instance to spawn a new instance of SQL Server under the user's context and attach the database specified to that new User Instance. The User Instance is a completely separate running instance of SQL Server form the main instance that is unique to the user and that will be shut down when there are no longer any connections to it.

This is totally different that attaching a database to the main instance, which stays running at all times, unless you've manually shut it down. If your question is about the main instance going into an Idle state, then your question is not unique to SQL Express and you should ask this question in the Database Engine forum. I believe all Editions of SQL Server have an Idle state and the other forum would be where you can find out how to affect that behavior.

The User Instance behavior is by design and it will not be changed for SQL 2005.

Regards,

Mike Wachal
SQL Express team

I'm not using user instances (neither user instance= true or attachdbfile....) and i'm still getting the errors....

Server resumed execution after being idle 1792 seconds. Reason: timer event.

Server resumed execution after being idle 1406 seconds: user activity awakened the server. This is an informational message only. No user action is required.

Please help!?!

Is it possible to prevent the server from going idle?

(given that i'm not using user instances)

I've got the same problem. I'm going to make a stupid thread in my app that dumps stuff into a table periodically to keep SQL Express awake. I'll let you know how well it works.