The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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?

asked 08 Nov '10, 08:39

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

edited 09 Nov '10, 21:30

1

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?

(08 Nov '10, 11:03) Breck Carter

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

(08 Nov '10, 17:14) Dmitri

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

(08 Nov '10, 17:24) Dmitri

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

permanent link

answered 30 Mar '12, 15:59

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

2

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 :).

(02 Apr '12, 04:39) Dmitri
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.

permanent link

answered 08 Nov '10, 17:20

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

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.

(08 Nov '10, 19:44) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×12

question asked: 08 Nov '10, 08:39

question was seen: 1,580 times

last updated: 02 Apr '12, 04:39