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
LVL 1
243johnmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

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.
0
geek_vjCommented:
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
0
Anthony PerkinsCommented:
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.
0
243johnmAuthor Commented:
Hi folks, sorry it took me so long to get back to this question, been really busy!  

First off, it wasn't Autoclose: I mentioned the users were able to log into the app.

I spent hours on this with the highest levels of tech support of the SW manufacturer. We discovered that:

1. When we logged into SSMS as the SW admin user, there were no problems. So that is why the users could continue to use the application.
2. If we logged into the server as the domain admin (what we had been doing all along), the DB's were not accessible when we opened up SSMS.
3. If we logged into the server as the machine admin, the DB's were accessible when we opened up SSMS.

We used (3) above to enable us to give the domain admin the sysadmin role in SSMS. Once that was done, all was fine: the domain admin had rights to the DB's, the SQL Server backup agent started working, etc.

One thing still stumps me and the TSR from the SW manufacturer was unable to give me an answer. As usual, I had installed the app and SQL Server logged into the server as the domain admin. But that login wasn't entered in the user list in SSMS! This is the only time I had ever seen this ... and he had never seen it before either.

Has anyone ever seen that before or have an idea why it happened?

Thanks very much,

John
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
243johnmAuthor Commented:
No answer received to my last question...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.