Sql-server – SQL Server 2008 R2 Express Connection

sql serversql-server-2008-r2

I installed SQL Server 2008 R2 Express Edition.

I opened the command prompt and typed sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword to connect to SQL Server.

However, it occasionally shows error messages such as 'login failed for user', or 'could not open a connection to SQL server'. When I install SQL Server 2008 R2 with Advanced Services, I can logon to SQL Server using SSMS successfully.

Best Answer

SQL Server Express greatly reduces the resources it uses if it is idle for a period of time. This may cause connections to time-out if the machine is busy after SQL Server idles.

http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx contains some details about this behavior, including:

When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory. This frees up memory for other applications to use.

This behavior makes sense for a single user database engine - it supports higher performance when the application using the data is running, but once that application is shut down, memory is released to allow other applications to use it. I like to think of this as "being a good citizen" in the application community. There is always a catch when making this kind of trade-off though, and SQL Express is no different in this aspect. When SQL Express transitions from idle to active some of the memory that was released needs to be reclaimed; this results is a slight lag during "startup time" when you're first connecting to SQL Express. You can actually see this happening in the Widows Application Log, when you first activate SQL Express after some idle time, an entry is written to the log that reads like this:

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

Also, SQL Server Express will "auto-close" any database that is no longer being used 300 milliseconds after activity ceases.

Related Question