Link to home
Start Free TrialLog in
Avatar of John Porter
John PorterFlag for United States of America

asked on

Losing connection to databases - 9 Databases on one instance SQL Server Express

Hello Experts,

I have created 1 Instances with 9 Databases using SQL Server 2008 R2 Express.

Problem:

I keep losing connection to one or more databases. (My front end APP fails because it loses connection to said databases and SSMS loses connection as well).

As a note - I have put up to four instances (One database per instance and never had a problem like this...

I understand there is a memory limitation of 1 Gb in SQL server Express.

Does anyone know what is happening here and how I can get around it?


Thanks!
ASKER CERTIFIED SOLUTION
Avatar of David Favor
David Favor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Porter

ASKER

Don't think this can be a connection limit issue as there is only one user connected (me).  How do I check the client + server logs?

Thanks!
Refer to your docs to determine where logs are deposited.

Likely someone else may be able to answer this off the top of their head.

Once you have your log location (maybe /var/log/*) be sure to add this to your notes, as log review usually is the first step in any debug session.
you may have set DB (s) on auto-close

it looks like what you have to save your sql server resources...


 ALTER DATABASE [yourDB] SET AUTO_CLOSE OFF  

more :
https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-auto-close-database-option-to-off?view=sql-server-2017

Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances
https://blogs.msdn.microsoft.com/sqlexpress/2008/02/22/understanding-sql-express-behavior-idle-time-resource-usage-auto_close-and-user-instances/
In SSMS you should see your server logs at Management » SQL Server Logs (you need to double click on the individual log entry to display its content).
Thanks