Hanging DB connections SQL server 2014

727
6
07-03-2018 12:34 PM
Highlighted
New Contributor II

Anyone have an issue with connecting to SQL server 2014 via sde database connection from Catalog/ArcMap?

desktop version: 10.6

SQL Server 2014

ArcGIS Server 10.5

Day one:

When computer A trys to open an MXD it cannot open and sits and spins. So I tried computer B to connect to computer A's db user connection, and was successful. In turn freeing up the connection on Computer A. 

Day two:

Computer A's mxd will not open/cannot establish db connection. I try to make connection from computer B, from a blank mxd, and it gets hung up, and keeps spinning. After closing both computer's mxd's, I try to hop on the database server (sql 2014) and make a connection from arcCatalog. It is a success. So I go back to computer B and the connection now works. Then I return to Computer A and the connection also works. (remember the progression)

Day three:

Computer A's db connection does not work, Computer B's attempt at the same connection does not work (nor do any other user connections) , so I try to connect from the server again, this time ArcCatalog gets hung up and will not make a connection. So I open SSMS on the server, and go to Activity log, and kill the process that is my hung up connection on the server. It instantly allows ArcCatalog to establish a connection. So my next step is to go to computer A, and try to make the connection, it does not work. So I go to computer B, and am able to make a connection. Now I go back to computer A and the connection now works. (seems like there is a relation between computer A and B, possibly) . So everyday since the beginning of this, I have had to go into ssms and kill the db conneciton processes. and repeat this process. 

This is a tax map office, and sometimes an mxd is shared, but only one person does editing, so we aren't using versioning. Any help would be greatly appreciated .

Thanks,

Mike

Reply
0 Kudos
6 Replies
Highlighted
Occasional Contributor III

Hi Mike,

We don't have any current defects out there that would explain the behavior you are seeing here. I just did a quick test with 2 of my machines here to see if I could replicate the issue and I can't. So I've got some questions for you:

  • Is this specific to one MXD? If you create a new MXD, does this same behavior happen?
  • Is it specific to these 2 machines (A & B)? If you find a machine "C", does this happen there as well? You mention this does not happen on the database server machine so curious if this works.
  • I see you mention opening an MXD and then also opening ArcCatalog. If you try this same workflow from ArcCatalog on machine A and B, does this behavior still occur?
  • Are your users closing these MXD's at the end of their work day or when they are done with them? Are the folks on machine B attempting to access the same MXD on the same shared network drive while machine A is using it? Or are they using it at different times?
  • Are there any ArcSOC processes running once a user closes ArcGIS completely on either machine A or B?

You can see I have more questions than anything at this point. But it helps to narrow things down.

Thanks,

Jonathan

Highlighted
New Contributor II

Hello Jonthathan!

-It is not specific to one mxd. This behavior also happens when I open a blank map.

-It is specific to these two machines, however in one my tests a computer C also got blocked out. However there is another user (computer D), who is at 10.5 who hasn’t been affected once throughout this entire process, even after catalog freezes up on the db server he is still connected. (He is also using the same database connection: GIS Owner

-The behavior still occurs when just opening ArcCatalog

-There are two MXD’s that are on a shared drive, that I recently put there. We were updating some custom add-ins and each add-in has to have it’s own mxd (by design). They are not accessed at the same time.

-There are no ArcSoc.exe processes running on machine A or B, however on our ArcGIS server, there are many ArcSoc processes running but they are all specific to certain services

-On my machine (comp B) I do notice that there are two “RuntimeLocalServer.exe (32 bit)” background processes running

Please let me know if you have other questions. Thanks so much for the help! ☺

-Mike

Reply
0 Kudos
Highlighted
Occasional Contributor III

Hi Mike,

Thanks for the reply. I agree with Josh's comment below that we need to narrow this down some more. It may actually be easier to have Technical Support open a case for this and investigate one on one with you. They'll be able to screen share with you and work on this in real time. You can call 888-377-4575 and we'll get a case created.

I'd like to know what you see when you connect as the SDE user (or the user you guys have specified as the geodatabase admin) and then go to Administration and Administer Geodatabase. What is there under the Connections and Locks tabs? Does it match what you would expect to see (is the number of connections and/or locks correct based on who you know is connected or is there another connection listed)? 

If you don't access the MXD's on the shared drive and both open new, blank MXD's from your own machines and make connections at the same time, what happens? Maybe this is related to those MXD's on the shared drive. 

- Jonathan

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

You need to isolate more where the hang-up is occurring, i.e., with the database client or the ArcGIS client or server application.  What happens when you use sqlcmd Utility | Microsoft Docs from each client using the credentials you are passing around between machines?  Do you see the same issue connecting to SQL Server outside of ArcGIS?

Reply
0 Kudos
Highlighted
New Contributor II

Hello all,

So the issue was resolved today. One of our systems analyst was having issues with hanging connections in logos. We believe there was an windows update that possibly triggered this. After changing the mac address to static, and restarting the server, computer A was able to connect without needing anyone else to connect. 

From our systems analyst

"We changed from a dynamic mac address on the vm to a static mac address.  This allows the communication to take place more efficiently"

http://www.amixa.com/blog/2014/06/29/inconsistent-ping-inconsistent-network-connectivity-on-hyper-v-...

I wish I could explain more, but I can't at the moment. We believe though this is was the cause of many recent issues on our SQL server. I'll keep you posted if  I find out anything more. 

Thanks,

Mike

Highlighted
Occasional Contributor III

Thanks for the update Michael! Glad you guys got things sorted out! 

Reply
0 Kudos