John Porter
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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/
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).
ASKER
Thanks
ASKER
Thanks!