Zombie Connections

A zombie connection is a runaway SQL Anywhere database connection that doesn't go away after a DROP CONNECTION. You can keep executing DROP CONNECTION statements and they won't return an error, but the won't do anything. The connection isn't of any use to any client application but from SQL Anywhere's point of view it's still running... it is a zombie connection.

Here's what a zombie connection looks like in the Foxhound Monitor page...

Reading upwards from the bottom, here's what happened...

• 8:57:55 AM The connection count (Parent/Child Conns) rose from 128 to 129 as "k.delacruz" started an ISQL session named "adhoc-queries".
  

• 8:58:06 AM The "k.delacruz" connection became a runaway with this faulty cross join:
  

  SELECT a.* INTO #temp_inventory FROM inventory AS a, inventory AS b

• 8:58:41 AM Foxhound has issued Alerts #21 and #22 because "k.delacruz" was using up temporary file space at a great rate.
  

• 8:59:26 AM Foxhound executed a DROP CONNECTION statement for the "k.delacruz" connection because of the temporary file usage.
  

  
  

• 9:03:07 AM It's three and half minutes later and all the Alerts have been cleared, but the "k.delacruz" connection has become a zombie... SQL Anywhere is still busy freeing up all that temporary file and rollback log space.
  

In the next Foxhound sample (not shown here) the connection count dropped back to 128 as "k.delacruz" finally disappeared. The moral of the story is this: It's OK to kill runaway connections, but don't try to deal with zombie connections; let SQL Anywhere do that job. Even if you kill the server, SQL Anywhere will still have to finish applying the rollback log during recovery.

---