Avatar of 243johnm
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
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
243johnm

8/22/2022 - Mon
Patrick Bogers

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.
geek_vj

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
Anthony Perkins

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
243johnm

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
243johnm

No answer received to my last question...