troubleshooting Question

SQL Server 2008 R2 Express Edition - DB's constantly go offline

Avatar of 243johnm
243johnm asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
5 Comments1 Solution307 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros