Failed to connect to database. Operation Failed

JustinConner · ‎09-16-2013

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 · ‎09-17-2013

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.

View solution in original post

WilliamCraft · ‎09-16-2013

I could be wrong, but this feels like it could be related to a limitation in the number of concurrent connections to the database based on the level of ArcGIS Server you are running. You mentioned that you are running Workgroup, which to my knowledge allows only for 10 concurrent users. Despite using SQL Server Standard rather than SQL Server Express (which is typically used with Workgroup), I think you are still limited by the Esri software in terms of the number of connections at one given time. Is it possible that your services, upon spinning up SOC instances per user requests, could be using up your available connections which causes the behavior you describe?

The article below on page 3 discusses the capacity limitations for ArcGIS Server levels. I know it is for 10.2, but I think if you dig hard enough you'll find something similar for 10.1 SP1.

http://www.esri.com/library/brochures/pdfs/arcgis-server-functionality-matrix.pdf

See if you can log into your database to see how many connections are being made to the geodatabase by Esri-related clients such as ArcGIS Desktop, Flex GIS applications, Python scripts, etc. This might tell you if you've hit some sort of maximum limit.

JustinConner · ‎09-17-2013

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.