SA10: cannot drop connections

[former_SQLA_member1694880]

SA 10.0.1.4116 / Win64 + mirroring.

Recently I've noticed some strange connections than hang around on server and cannot be dropped: an attempt to disconnect them using DROP CONNECTION statement yields 'User "DBA" dropped connection xxx' message on console, but connection itself stays intact. How can I get rid of them without server restart?

[Breck_Carter]

Please show us an sa_conn_info() display. I'm just guessing but they may be internal processes that weren't well documented in 10. Does the connection number stay the same, or do they immediately come back to life with a different number?

[former_SQLA_member1694880]

Hmmm... not sure how I can make them readable, but anyway I'll try...

[former_SQLA_member1694880]

A bit of afterthought: can internal processes hang around for days? I've never seen it before...

[jeff_albion · Accepted solution]

There is a known issue (CR #702733 - 11.0.1.2786, 12.0.1.3713) which allows connections to be 'undroppable' in rare circumstances (usually involving a parallelized query that makes use of a HashFilter). Unfortunately, this fix has not been backported to the 10.0.1 codeline due to 10.0.1's "Archived" or "End-of-Life" status. Setting the connection option 'MAX_QUERY_TASKS' to '1' or restarting the database server is a workaround for the issue in 10.0.1.

For more information about this issue, see my response to this question here: http://sqlanywhere-forum.sap.com/questions/6164/connections-that-cannot-be-dropped/6197

[former_SQLA_member1694880 · Accepted solution]

I'm now on 10.0.1.4239, and I naven't encountered 'undroppable' connections for quite a time, so my problem now appears fixed. But thanks for the info anyway :).

[former_SQLA_member1694880]

select * from sa_conn_info() ;

number  name    userid  dbnumber    lastreqtime reqtype commlink    nodeaddr    clientport  serverport  blockedon   lockrowid   lockindexid locktable   uncommitops
4   SQL_DBC_1df2ef8 robot   0   2010-11-08 20:08:19.641000  CLOSE   TCPIP   192.168.130.190 4164    2638    0   0           0  
5   SQL_DBC_24129a0 robot   0   2010-11-08 19:59:14.873000  COMMIT  TCPIP   192.168.130.190 4166    2638    0   0           0  
13404   SQL_DBC_1f54e58 user1   0   2010-10-05 11:19:45.209000  FETCH   TCPIP   192.168.130.105 1039    2638    0   0           0  
50732   SQL_DBC_d343f0  DBA 0   2010-11-08 20:08:27.705000  CLOSE   TCPIP   192.168.130.190 1106    2638    0   0           2363966  
50733   SQL_DBC_15c0b690    Ishop   0   2010-11-08 20:08:28.985000  COMMIT  TCPIP   192.168.130.10  59468   2638    0   0           0  
50736   SQL_DBC_1b54a10 user2   0   2010-11-08 19:43:22.425000  CLOSE   TCPIP   192.168.130.43  2878    2638    0   0           0  
50738   SQL_DBC_4d6c5c0 DBA 0   2010-11-08 18:17:41.689000  COMMIT  TCPIP   192.168.130.8   3604    2638    0   0           0  
50739   ASACIS_1057513657:fortsbase:fortsserver:37056000_8706   FortsReciever   0   2010-11-08 20:08:26.681000  COMMIT  TCPIP   192.168.130.170 2325    2638    0   0           0  
50740   SQL_DBC_9c45e0  Gate    0   2010-11-08 20:08:19.001000  CLOSE   TCPIP   192.168.130.141 2179    2638    0   0           0  
50744   SQL_DBC_19a4760 DBA 0   2010-11-08 20:08:28.473000  COMMIT  TCPIP   192.168.130.170 2349    2638    0   0           0  
50746       DealsAccomplishBot  0   2010-11-08 20:08:29.113000  COMMIT  TCPIP   192.168.130.141 2205    2638    0   0           0  
50826   SQL_DBC_4e22008 DBA 0   2010-11-08 18:17:40.409000  COMMIT  TCPIP   192.168.130.8   3661    2638    0   0           0  
51999       DBA 0   2010-11-08 18:56:49.977000  COMMIT  TCPIP   192.168.130.170 4491    2638    0   0           0  
52004       DBA 0   2010-11-08 19:01:15.961000  COMMIT  TCPIP   192.168.130.170 4497    2638    0   0           0  
52013   SQL_DBC_27ba7d0 user3   0   2010-11-08 20:04:28.345000  COMMIT  TCPIP   192.168.130.2   1125    2638    0   0           0  
52014   SQL_DBC_27b4f48 user3   0   2010-11-08 20:08:29.241000  FETCH   TCPIP   192.168.130.2   1126    2638    0   0           0  
52015   SQL_DBC_1c94ad0 user4   0   2010-11-08 20:08:10.169000  CLOSE   TCPIP   192.168.130.20  2251    2638    0   0           0  
1018011895          0       unknown (0) NA  NA  0   0   0   0           0  
1018011896          0       unknown (0) NA  NA  0   0   0   0           0  
1018011897          0       unknown (0) NA  NA  0   0   0   0           0  
1018011898          0       unknown (0) NA  NA  0   0   0   0           0  
1018011899          0       unknown (0) NA  NA  0   0   0   0           0  
1018011900          0       unknown (0) NA  NA  0   0   0   0           0  
1018011901          0       unknown (0) NA  NA  0   0   0   0           0  
1018011902          0       unknown (0) NA  NA  0   0   0   0           0  

Connections ## 13404 and 1018011895 through 1018011902 are 'undroppable', that is, DROP CONNECTION has no effect on them (first one holds some shared locks - they are still there). Actual user names changed to USERx for a bit of anonimity.

[Breck_Carter]

I'm hoping Graeme Perrow will step in here; in the meantime, check out this 12.0.0 doc and scroll down to "Name" to see a list of magic connections... dunno if what you're seeing in the high-numbers are in that list (no connection names in V10 or 11). Also, that user connection not being droppable, that is a puzzler.