2176026 - A user process became a zombie process that can't be killed - SAP ASE

• Observing a long running transaction in ASE.
• Querying syslogs hold shows long running transaction (in this example, spid 395):

select * from syslogshold
go
dbid   reserved    spid   page       xactid         masterxactid   starttime                       name                                                                xloid
------ ----------- ------ ---------- -------------- -------------- ------------------------------- ------------------------------------------------------------------- -----------
          10           0    395   44341015 0x1797a4020700 0x000000000000             May 12 20XX  9:32AM $del                                                                        790    

• There is no IO or CPU movement on spid 395. 
• In this example, sysprocesses shows cpu has a value of 4 and physical_io has 0:

select * from master..sysprocesses where spid = 395
go
 spid   kpid        enginenum   status       suid        hostname                       program_name                   hostprocess                    cmd                            cpu         physical_io memusage    blocked dbid   uid         gid         tran_name                                                        time_blocked network_pktsz fid    execlass                       priority   affinity                       id          stmtnum     linenum     origsuid    block_xloid clientname                     clienthostname                 clientapplname                 sys_id ses_id      loggedindatetime                ipaddr                                                           nodeid
 ------ ----------- ----------- ------------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------- ----------- ----------- ------- ------ ----------- ----------- ---------------------------------------------------------------- ------------ ------------- ------ ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------ ----------- ------------------------------- ---------------------------------------------------------------- ------
    395   668009160           3 runnable               5 xxxxxxxx                      xxxxxxxx               3277                           DELETE                                   4           0           7       0     10           1           0 $del                                                                     NULL           512      0 EC2                            MEDIUM     syb_default_pool                1368857408          51         335        NULL           0                                                                                                   0           0             May 12 20XX  9:32AM xx.xx.xx.xx                                                        NULL

• Repeating the query a minute later shows the cpu and physical_io values have not changed for this spid:

select * from master..sysprocesses where spid = 395
go
 spid   kpid        enginenum   status       suid        hostname                       program_name                   hostprocess                    cmd                            cpu         physical_io memusage    blocked dbid   uid         gid         tran_name                                                        time_blocked network_pktsz fid    execlass                       priority   affinity                       id          stmtnum     linenum     origsuid    block_xloid clientname                     clienthostname                 clientapplname                 sys_id ses_id      loggedindatetime                ipaddr                                                           nodeid
 ------ ----------- ----------- ------------ ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------- ----------- ----------- ------- ------ ----------- ----------- ---------------------------------------------------------------- ------------ ------------- ------ ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------ ----------- ------------------------------- ---------------------------------------------------------------- ------
    395   668009160           3 runnable               5 xxxxxxxx                      xxxxxxxx               3277                           DELETE                                   4           0           7       0     10           1           0 $del                                                                     NULL           512      0 EC2                            MEDIUM     syb_default_pool                1368857408          51         335        NULL           0                                                                                                   0           0             May 12 20XX  9:32AM xx.xx.xx.xx                                                        NULL

• Querying syslocks does not show any locks for the spid:

1> select * from syslocks where spid = 395
2> go
 id          dbid   page       type   spid   class                          fid    context row    loid        partitionid nodeid
 ----------- ------ ---------- ------ ------ ------------------------------ ------ ------- ------ ----------- ----------- ------

• SAP Adaptive Server Enterprise (ASE) 15.7
  
• SAP Adaptive Server Enterprise (ASE) 16.x

zombie, hung , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , How To