243johnm
asked on
SQL Server 2008 R2 Express Edition - DB's constantly go offline
I recently installed an application that uses & installs SQL Server 2008 R2 SP1 on a new server running Windows Server 2008 R2 that runs only this app.
A couple of days after install, there were major issues with SQL Server. I completely uninstalled then reinstalled both the app & SQL Server.
There were more probs this week. Users were not able to login to the app. When I logged onto the server this past Thursday, the DBs were all offline. I was finally able to get the DB's online (this required multiple restarts of the SQL Server service) and made a SQL backup (the client's infrastructure consultants had not installed Backup SW yet).
I created a new DB and restored the backup into it. It looked good so we crossed our fingers. The users were able to login yesterday.
Today, I tried to login to the app but IE came back with an error message that the URL was unavailable. I then logged onto the clients server. The new DB I had created was offline again! This is the SSMS message:
---
TITLE: Microsoft SQL Server Management Studio
--------------------------
The database Hxxxyyyzzz2013 is not accessible. (ObjectExplorer)
---
I rebooted the server and took another look. Same problem, DB's were offline!
In services, both SQL Server instances are listed as started.
I couldn't find any errors in the Event Viewer for today except this: "Report Server Windows Service cannot connect to the report server database."
After the reboot, despite the fact that DB`s are still listed as offline in SSMS, I was able to:
1. Log into the Windows app and the Admin module
2, Log into the Web App as a user
That makes absolutely no sense to me!
I looked for clues in the ERRORLOG files.
In ERRORLOG, I found this just after the reboot when I clicked on the primary User DB in SSMS:
2013-08-31 10:58:42.78 spid53 Starting up database 'Hxxxyyyzzz2013'.
2013-08-31 10:58:43.91 Logon Error: 18456, Severity: 14, State: 38.
2013-08-31 10:58:43.91 Logon Login failed for user 'Domain\user'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.8]
In ERRORLOG.3 & ERRORLOG.4, I found this repeated thousands of times
2013-08-23 21:15:57.93 spid58 Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:00.55 spid27s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:01.12 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:01.90 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:02.40 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:03.19 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:03.66 spid35s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:04.41 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:04.93 spid34s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:05.56 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:06.17 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:06.65 spid37s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:07.43 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:07.92 spid40s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:08.57 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:09.15 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:09.84 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:10.37 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:10.85 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:11.39 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:12.08 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:12.58 spid43s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:13.36 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:13.84 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:14.60 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:15.11 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:15.61 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:16.12 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:16.86 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:17.36 spid26s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:17.89 spid43s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:18.37 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:19.09 spid45s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:19.59 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:20.13 spid44s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:20.63 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:21.34 spid45s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:21.83 spid26s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:22.41 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:22.88 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:23.61 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:24.09 spid34s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:24.67 spid44s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:25.13 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:25.90 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:26.35 spid37s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:26.92 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:27.39 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:28.14 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:28.60 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:29.17 spid43s Starting up database 'Hxxxyyyzzz'.
Before the backup & restore into a new DB (I called it Hxxxyyyzzz2013), Hxxxyyyzzz was the primary DB used by the app I installed.
I checked the ERRORLOG files on our server (SQL Server 2005 Workgroup Edition). I couldn't find any repeated messages like that...
I have installed this application on dozens of servers over the years and have never had this kind of problem crop up before. In fact, I did an install of the exact same version just after this install and that install is working just fine!
Any ideas as to cause or other ideas about what to look for are very welcome!
Thanks very much,
John
A couple of days after install, there were major issues with SQL Server. I completely uninstalled then reinstalled both the app & SQL Server.
There were more probs this week. Users were not able to login to the app. When I logged onto the server this past Thursday, the DBs were all offline. I was finally able to get the DB's online (this required multiple restarts of the SQL Server service) and made a SQL backup (the client's infrastructure consultants had not installed Backup SW yet).
I created a new DB and restored the backup into it. It looked good so we crossed our fingers. The users were able to login yesterday.
Today, I tried to login to the app but IE came back with an error message that the URL was unavailable. I then logged onto the clients server. The new DB I had created was offline again! This is the SSMS message:
---
TITLE: Microsoft SQL Server Management Studio
--------------------------
The database Hxxxyyyzzz2013 is not accessible. (ObjectExplorer)
---
I rebooted the server and took another look. Same problem, DB's were offline!
In services, both SQL Server instances are listed as started.
I couldn't find any errors in the Event Viewer for today except this: "Report Server Windows Service cannot connect to the report server database."
After the reboot, despite the fact that DB`s are still listed as offline in SSMS, I was able to:
1. Log into the Windows app and the Admin module
2, Log into the Web App as a user
That makes absolutely no sense to me!
I looked for clues in the ERRORLOG files.
In ERRORLOG, I found this just after the reboot when I clicked on the primary User DB in SSMS:
2013-08-31 10:58:42.78 spid53 Starting up database 'Hxxxyyyzzz2013'.
2013-08-31 10:58:43.91 Logon Error: 18456, Severity: 14, State: 38.
2013-08-31 10:58:43.91 Logon Login failed for user 'Domain\user'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.8]
In ERRORLOG.3 & ERRORLOG.4, I found this repeated thousands of times
2013-08-23 21:15:57.93 spid58 Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:00.55 spid27s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:01.12 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:01.90 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:02.40 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:03.19 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:03.66 spid35s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:04.41 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:04.93 spid34s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:05.56 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:06.17 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:06.65 spid37s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:07.43 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:07.92 spid40s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:08.57 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:09.15 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:09.84 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:10.37 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:10.85 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:11.39 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:12.08 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:12.58 spid43s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:13.36 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:13.84 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:14.60 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:15.11 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:15.61 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:16.12 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:16.86 spid28s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:17.36 spid26s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:17.89 spid43s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:18.37 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:19.09 spid45s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:19.59 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:20.13 spid44s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:20.63 spid30s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:21.34 spid45s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:21.83 spid26s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:22.41 spid31s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:22.88 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:23.61 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:24.09 spid34s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:24.67 spid44s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:25.13 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:25.90 spid36s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:26.35 spid37s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:26.92 spid25s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:27.39 spid29s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:28.14 spid38s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:28.60 spid32s Starting up database 'Hxxxyyyzzz'.
2013-08-23 21:16:29.17 spid43s Starting up database 'Hxxxyyyzzz'.
Before the backup & restore into a new DB (I called it Hxxxyyyzzz2013), Hxxxyyyzzz was the primary DB used by the app I installed.
I checked the ERRORLOG files on our server (SQL Server 2005 Workgroup Edition). I couldn't find any repeated messages like that...
I have installed this application on dozens of servers over the years and have never had this kind of problem crop up before. In fact, I did an install of the exact same version just after this install and that install is working just fine!
Any ideas as to cause or other ideas about what to look for are very welcome!
Thanks very much,
John
Last Comment
Looks like the Autoclose is set to ON for the databases.
If yes, change to OFF as setting it to ON have adverse effects on production databases.
You can change it through properties of the database or through query. Please find the below link for more details:
http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
If yes, change to OFF as setting it to ON have adverse effects on production databases.
You can change it through properties of the database or through query. Please find the below link for more details:
http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
As suggested previously, this is not a case of the database going OFFLINE, but rather that it is closed and you need to verify that you do not have Auto-Close on. A database going OFFLINE is quite another story and you would find entries of that in your Error Log. This is different.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
No answer received to my last question...
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Have a look at the properties of your database. If "AutoClose" is ON the database will shut down after the last connection is closed.
I recommended to set Autoclose to false and try again.
State 38 means user doesn't have access to database it is trying to connect to so you would have to check the connectionstring from the app.