Why does it take so long to connect to a specific database and not the others?

[SH013]

Has anyone encounter this issue described below:

I have seen this happen frequently within the last few days and it seems to start to pick up its momentum, occurring more often than ever. When I am in ArcMap, I made an attempt to connect to a specific enterprise geodatabase. It would take a lengthy time to connect to the database. You would see an hourglass load forever and ArcMap will appear as if it is not responding. But that is not the case, because the connection will eventually connect. In terms of when it will connect, it is very inconsistent. It can sometimes take 5 minutes, 10 minutes, 1 hour, 2 hours and can even be more than that.

This is the only database that has this particular problem.  When the issue occurs, I can still connect to other databases without any issue. I will admit that it is also the database that is being utilized the most.  I would say there are at least 5-10 users connecting to the database and from different locations coming from US and Canada.

This issue will come and go and seem to resolve on its on.  After speaking to our dba, they seem to hint there is some sort of blocking mechanism taking place from the SQL Server end. This is why it is preventing users from connecting to the database. We are still unable to determine the root cause of this.  When we connect through SSMS, we do not have this issue. This issue only occurs when users are connecting from ArcMap. 

Just hoping to see if someone has seen this issue before as we continue to investigate where the root cause is.

Thanks

[George_Thompson]

What version is the enterprise geodatabase at?

What version of ArcGIS Desktop and SQL Server are being used?

What is the level of SQL Server being used?

Based on your post, it seems that it is in the network or the DB. Are the other geodatabases that you can access quickly on the same SQL Server instance or a separate instance?

Geodatabase‌ Enterprise GIS‌

[SH013]

Hi there, thanks for your input. I don't believe this is a network issue, because we have users from different cities connecting to it. They are all having the same issue. when the issue is occurring, I can still access all other databases within the same instance. Again I reiterate that this is a database specific issue; hence why I do believe that this is more of a DB issue rather than a network issue.

Our environment is as followed:

- ArcGIS Desktop 10.4.1

- Enterprise Geodatabase at 10.4.1

- SQL Server 2014

I have provided an attachment of a report that shows that transactions with the specified database are being blocked.

[PeterKing]

Also, to piggyback on George's reply: Do any of the other 5 to 10 distributed users experience the same issue as you? 

[SH013]

Hi Peter,

just to respond to your question, when the issue occurs, it will impact all users regardless of where they are.

Thanks.

[Thiru_P]

Hi Sam,

How old is the database?

I had similar issue with a database that had too much old geoprocessing history and old metadata.

Ask your DBA to do a backup before you do this (Just in case)

If you want to remove the geoprocessing history from the database, use the following tool

Clear Geoprocessing History (Pre10.5)

https://www.arcgis.com/home/item.html?id=45a59e9590484709bca7b5de83d957ce 

You can run the tool in batch mode if there are many feature classes in your SDE.

If you are able to run python scripts, you can refer to the following article.

SDE root takes forever to load? Check Documentation field on GBD_Items table (SDE WORKSPACE METADATA... 

Let me know if this resolves the issue.

Good luck,

Thiru

[SH013]

Thanks for your input. However, we have already identified this issue before and have already removed all outdated geoprocessing history and old metadata using the customized python script. The outdated metadata was causing our data to load very slow in ArcMap. This time around, users are simply not able to make connection to the database. 

[George_Thompson]

Can you also make sure that you have the latest patches on your desktop client? I saw a few patches for SQL Server specific, I would recommend installing them both and re-testing.

[SH013]

Hi George, just be ensure that we have the latest patches, do you mind providing the list here for us to verify? Many thanks