Failed to connect to database. Operation Failed

JustinConner

Details:
Windows Server 2008 R2 Standard SP1 64 Bit
4 GB RAM
MS SQL Server 2008 R2 version 10.50.1600.1
ArcGIS Server Workgroup 10.1 SP1

I periodically get the "Failed to connect to database. Operation Failed " error when trying to connect in ArcCatalog or ArcMap. I can log into to SQL Server Management Studio and view the database and it's tables but it wont connect in ArcGIS. The only solution that works is to go into SQL Server and "Take Offline" and then "Bring Online". That will work for awhile but it eventually fails to connect, usually within the day. One interesting thing is that my "WebData" database has never had this issue. This is a child replica of my "WoodCountyGIS" database which does have the issue. WebData is the database that supports all of my map services that run the website. I've looked in Management Studio to compare the two database properties and they both look identical.

On a check of the server logs I see

The log for database 'ReportServer$SQLEXPRESS' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

. In the last 40 minutes this error message appears 16 times, the time intervals are not regularly spaced. When the geodatabase is throwing the connection error the log will say the same thing except change ReportServer with my database name.

I have a request in with support but I thought maybe someone out here would have a thought as well. Thanks!

JustinConner

Thanks Crafty, that's a good thought and I'll explore that path.

I checked my connections this morning and they are all working. Now that's not to say that I solved the problem but I'm hopeful. On a check of the SQL logs I found no errors, just the usual information messages.

Here's what I did. Disclaimer first, I am not a SQL expert and the things I tried were the result of a lot of Google searches.

Since taking the database offline and bringing it back online worked, that led me to this http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases. SDE defaults have new databases set as "Auto Close = True" which means that when a DB is not used it closes. Since the DB that runs the map services never had a problem, changing this setting to False made sense to me.

After making the above change I spotted another error that was being overlooked due to the amount of other errors.

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

That led me to this http://msdn.microsoft.com/en-us/library/ms190730.aspx

I made those two changes and now my databases react much quicker. It no longer takes 5 or 10+ seconds to open a database. If I dont reply on this thread assume the fix worked.