Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

what is teh most reliable way to have the list of orphan spid

11 posts in General Discussion Last posting was on 2012-04-10 20:09:41.0Z
tartampion Posted on 2012-04-09 13:57:20.0Z
Sender: 53f6.4f82e9cb.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: what is teh most reliable way to have the list of orphan spid
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f82eac0.5412.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Apr 2012 06:57:20 -0700
X-Trace: forums-1-dub 1333979840 172.20.134.41 (9 Apr 2012 06:57:20 -0700)
X-Original-Trace: 9 Apr 2012 06:57:20 -0700, 172.20.134.41
Lines: 2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31015
Article PK: 73903

Is there any other way but rebooting teh serevr to get rid
of orphan spid?


Rob V Posted on 2012-04-09 20:16:07.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f82eac0.5412.1681692777@sybase.com>
In-Reply-To: <4f82eac0.5412.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f834387$1@forums-1-dub>
Date: 9 Apr 2012 13:16:07 -0700
X-Trace: forums-1-dub 1334002567 10.22.241.152 (9 Apr 2012 13:16:07 -0700)
X-Original-Trace: 9 Apr 2012 13:16:07 -0700, vip152.sybase.com
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31016
Article PK: 73904


On 09-Apr-2012 15:57, tartampion wrote:
> Is there any other way but rebooting teh serevr to get rid
> of orphan spid?

What do you mean with an "orphan spid"?
usually this sort of question is about "orphan tables" which means; a
table ownsed by a spid which is no longer there. Is that what you mean?

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


tartampion Posted on 2012-04-09 20:23:43.0Z
Sender: 5417.4f82ead6.1804289383@sybase.com
From: Tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f83454f.60d0.1681692777@sybase.com>
References: <4f834387$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Apr 2012 13:23:43 -0700
X-Trace: forums-1-dub 1334003023 172.20.134.41 (9 Apr 2012 13:23:43 -0700)
X-Original-Trace: 9 Apr 2012 13:23:43 -0700, 172.20.134.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31019
Article PK: 73912

I mean a spid which has lost connection to the application
which created it , due to abnormal disconnect etc. I mean a
Zombie spid.

> On 09-Apr-2012 15:57, tartampion wrote:
> > Is there any other way but rebooting teh serevr to get
> > rid of orphan spid?
>
> What do you mean with an "orphan spid"?
> usually this sort of question is about "orphan tables"
> which means; a table ownsed by a spid which is no longer
> there. Is that what you mean?
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------


Mark Posted on 2012-04-09 20:37:38.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f834387$1@forums-1-dub> <4f83454f.60d0.1681692777@sybase.com>
In-Reply-To: <4f83454f.60d0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f834892@forums-1-dub>
Date: 9 Apr 2012 13:37:38 -0700
X-Trace: forums-1-dub 1334003858 10.22.241.152 (9 Apr 2012 13:37:38 -0700)
X-Original-Trace: 9 Apr 2012 13:37:38 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31020
Article PK: 73909

kill [spid]

On 4/9/2012 4:23 PM, Tartampion wrote:
> I mean a spid which has lost connection to the application
> which created it , due to abnormal disconnect etc. I mean a
> Zombie spid.
>
>> On 09-Apr-2012 15:57, tartampion wrote:
>>> Is there any other way but rebooting teh serevr to get
>>> rid of orphan spid?
>> What do you mean with an "orphan spid"?
>> usually this sort of question is about "orphan tables"
>> which means; a table ownsed by a spid which is no longer
>> there. Is that what you mean?
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------


tartampion Posted on 2012-04-09 20:41:56.0Z
Sender: 5417.4f82ead6.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f834994.6173.1681692777@sybase.com>
References: <4f834892@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Apr 2012 13:41:56 -0700
X-Trace: forums-1-dub 1334004116 172.20.134.41 (9 Apr 2012 13:41:56 -0700)
X-Original-Trace: 9 Apr 2012 13:41:56 -0700, 172.20.134.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31021
Article PK: 73915

We all know that kill doe snot work, the zombie spid is not
in master..sysprocesses, how do you want to kill it.

> kill [spid]
>
> On 4/9/2012 4:23 PM, Tartampion wrote:
> > I mean a spid which has lost connection to the
> > application which created it , due to abnormal
> > disconnect etc. I mean a Zombie spid.
> >
> >> On 09-Apr-2012 15:57, tartampion wrote:
> >>> Is there any other way but rebooting teh serevr to get
> >>> rid of orphan spid?
> >> What do you mean with an "orphan spid"?
> >> usually this sort of question is about "orphan tables"
> >> which means; a table ownsed by a spid which is no
> longer >> there. Is that what you mean?
> >>
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Professional DBA for Sybase ASE, IQ,
> Replication >> Server
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
> (new!) >> "The Complete Sybase ASE Quick Reference Guide"
> >> "The Complete Sybase Replication Server Quick Reference
> >> Guide"
> >>
> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >> Commerce 27138666
> >>
> ----------------------------------------------------------
> >> -------


Bret Halford Posted on 2012-04-09 23:07:36.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f834892@forums-1-dub> <4f834994.6173.1681692777@sybase.com>
In-Reply-To: <4f834994.6173.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f836bb8$1@forums-1-dub>
Date: 9 Apr 2012 16:07:36 -0700
X-Trace: forums-1-dub 1334012856 10.22.241.152 (9 Apr 2012 16:07:36 -0700)
X-Original-Trace: 9 Apr 2012 16:07:36 -0700, vip152.sybase.com
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31023
Article PK: 73910


On 4/9/2012 2:41 PM, tartampion wrote:
> We all know that kill doe snot work, the zombie spid is not
> in master..sysprocesses, how do you want to kill it.
>> kill [spid]

>>>I mean a spid which has lost connection to the application
>>>which created it , due to abnormal disconnect etc. I mean a
>>>Zombie spid.

We really don't all know that. Rob asked you to clarify
what you meant by the term for good reason. There are a number
of situations that you could have been talking about, the
terminology is often confused although what is going
on is quite different.

In the case you seemingly described, "the client application
has lost it's network connection to the server",
the spid usually is in sysprocesses, usually "awaiting command",
may or may not be holding resources. kill usually works just fine
against it. The network layer will also usually clean it up, given time
(the default setting for TCP Keepalive is generally 2 hours).

A zombie spid usually refers to a spid that exists in sysprocesses
but the kill comand is not effective on it - usually because it
is in some sleep state, such as waiting for an OS event (network
or disk i/o). [zombies have bodies (entries in sysprocesses)
but cannot be killed - they just keep on existing]. The KILL
command just sets a status in the spid's process; ASE depends on the
spid to clean itself up when it wakes up from it's sleep condition.

If you are talking about a case where there are locks in syslocks
held by a spid that doesn't show up in sysprocesses, I would call
that a phantom lock, not a zombie spid. [phantoms are dead and don't
have bodies (entries in sysprocesses), there is nothing to kill
but shadows (locks) it has left behind).

You are trying to throw holy water at the wrong target.
The process is already gone, it is the lock you want to get rid of.
You may be able to use dbcc dbreboot on the database to clear the issue
without rebooting the entire server

dbcc traceon(3604)
go
dbcc dbreboot("shutdown", <dbname>)
go
dbcc dbreboot("reboot", <dbname>)
go


"Mark A. Parsons" <iron_horse Posted on 2012-04-09 21:51:11.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f82eac0.5412.1681692777@sybase.com>
In-Reply-To: <4f82eac0.5412.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f8359cf$1@forums-1-dub>
Date: 9 Apr 2012 14:51:11 -0700
X-Trace: forums-1-dub 1334008271 10.22.241.152 (9 Apr 2012 14:51:11 -0700)
X-Original-Trace: 9 Apr 2012 14:51:11 -0700, vip152.sybase.com
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31022
Article PK: 73911

I ran into a similar situation last week at a client ... [ASE 15.5] stack trace killed off a spid but left an open
transaction and a few locks in the dataserver.

dbcc dbreboot failed to restart the database. Had to bounce the dataserver.

Per insistence from someone at the client I opened a case with Sybase tech support to see if there has been any recent
developments (ie, any new methods/dbcc's) to allow the killing of an orphan txn/locks ... and the answer was 'No, you
have to reboot the dataserver.'.

On 04/09/2012 07:57, tartampion wrote:
> Is there any other way but rebooting teh serevr to get rid
> of orphan spid?


tartampion Posted on 2012-04-10 15:43:46.0Z
Sender: 1008.4f842d5a.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f845532.16dd.1681692777@sybase.com>
References: <4f8359cf$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Apr 2012 08:43:46 -0700
X-Trace: forums-1-dub 1334072626 172.20.134.41 (10 Apr 2012 08:43:46 -0700)
X-Original-Trace: 10 Apr 2012 08:43:46 -0700, 172.20.134.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31024
Article PK: 73913

Thanks all for your input, I agree with Mark that Sybase
should propose a solution which permits to get rid of Zombie
spid without a need to reboot the server.
I still would like to know what is the best way to get the
list of Zombie spids:
Would the following work in all cases?
select * from master..syslocks where spid not in (select
spid from master..sysprocesses)
but according to Bret the zombie spid is in sysprocesses, I
am really confused, please shed some light.

,

> I ran into a similar situation last week at a client ...
> [ASE 15.5] stack trace killed off a spid but left an open
> transaction and a few locks in the dataserver.
>
> dbcc dbreboot failed to restart the database. Had to
> bounce the dataserver.
>
> Per insistence from someone at the client I opened a case
> with Sybase tech support to see if there has been any
> recent developments (ie, any new methods/dbcc's) to allow
> the killing of an orphan txn/locks ... and the answer was
> 'No, you have to reboot the dataserver.'.
>
> On 04/09/2012 07:57, tartampion wrote:
> > Is there any other way but rebooting teh serevr to get
> > rid of orphan spid?


peta62 Posted on 2012-04-10 17:38:35.0Z
From: peta62 <no@mail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f8359cf$1@forums-1-dub> <4f845532.16dd.1681692777@sybase.com>
In-Reply-To: <4f845532.16dd.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f84701b$1@forums-1-dub>
Date: 10 Apr 2012 10:38:35 -0700
X-Trace: forums-1-dub 1334079515 10.22.241.152 (10 Apr 2012 10:38:35 -0700)
X-Original-Trace: 10 Apr 2012 10:38:35 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31025
Article PK: 73914

Hello,
first I think your command will work just fine, I use it.
second please read again thoroughly what Bret wrote, especially the
difference between zombies and phantoms/ghosts. There is nothing anybody
could say better than Bret about it.
HTH,
Peter

On 4/10/2012 5:43 PM, tartampion wrote:
> Thanks all for your input, I agree with Mark that Sybase
> should propose a solution which permits to get rid of Zombie
> spid without a need to reboot the server.
> I still would like to know what is the best way to get the
> list of Zombie spids:
> Would the following work in all cases?
> select * from master..syslocks where spid not in (select
> spid from master..sysprocesses)
> but according to Bret the zombie spid is in sysprocesses, I
> am really confused, please shed some light.
>
> ,
>> I ran into a similar situation last week at a client ...
>> [ASE 15.5] stack trace killed off a spid but left an open
>> transaction and a few locks in the dataserver.
>>
>> dbcc dbreboot failed to restart the database. Had to
>> bounce the dataserver.
>>
>> Per insistence from someone at the client I opened a case
>> with Sybase tech support to see if there has been any
>> recent developments (ie, any new methods/dbcc's) to allow
>> the killing of an orphan txn/locks ... and the answer was
>> 'No, you have to reboot the dataserver.'.
>>
>> On 04/09/2012 07:57, tartampion wrote:
>>> Is there any other way but rebooting teh serevr to get
>>> rid of orphan spid?


tartampion Posted on 2012-04-10 20:09:41.0Z
Sender: 2036.4f848ee5.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f849385.2100.1681692777@sybase.com>
References: <4f84701b$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Apr 2012 13:09:41 -0700
X-Trace: forums-1-dub 1334088581 172.20.134.41 (10 Apr 2012 13:09:41 -0700)
X-Original-Trace: 10 Apr 2012 13:09:41 -0700, 172.20.134.41
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31027
Article PK: 73916

Agreed, I thank you all, and Bret in particular, he is
always not only crystal clear but also profoundly technical.
I indeed read and reread his post.

> Hello,
> first I think your command will work just fine, I use
> it.
> second please read again thoroughly what Bret wrote,
> especially the difference between zombies and
> phantoms/ghosts. There is nothing anybody could say
> better than Bret about it. HTH,
> Peter
>
>
> On 4/10/2012 5:43 PM, tartampion wrote:
> > Thanks all for your input, I agree with Mark that Sybase
> > should propose a solution which permits to get rid of
> > Zombie spid without a need to reboot the server.
> > I still would like to know what is the best way to get
> > the list of Zombie spids:
> > Would the following work in all cases?
> > select * from master..syslocks where spid not in (select
> > spid from master..sysprocesses)
> > but according to Bret the zombie spid is in sysprocesses
> > , I am really confused, please shed some light.
> >
> > ,
> >> I ran into a similar situation last week at a client
> .. >> [ASE 15.5] stack trace killed off a spid but left an
> open >> transaction and a few locks in the dataserver.
> >>
> >> dbcc dbreboot failed to restart the database. Had to
> >> bounce the dataserver.
> >>
> >> Per insistence from someone at the client I opened a
> case >> with Sybase tech support to see if there has been
> any >> recent developments (ie, any new methods/dbcc's)
> to allow >> the killing of an orphan txn/locks ... and the
> answer was >> 'No, you have to reboot the dataserver.'.
> >>
> >> On 04/09/2012 07:57, tartampion wrote:
> >>> Is there any other way but rebooting teh serevr to get
> >>> rid of orphan spid?
>


Bret Halford Posted on 2012-04-10 18:30:09.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: what is teh most reliable way to have the list of orphan spid
References: <4f8359cf$1@forums-1-dub> <4f845532.16dd.1681692777@sybase.com>
In-Reply-To: <4f845532.16dd.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f847c31@forums-1-dub>
Date: 10 Apr 2012 11:30:09 -0700
X-Trace: forums-1-dub 1334082609 10.22.241.152 (10 Apr 2012 11:30:09 -0700)
X-Original-Trace: 10 Apr 2012 11:30:09 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31026
Article PK: 73917


On 4/10/2012 9:43 AM, tartampion wrote:
> Thanks all for your input, I agree with Mark that Sybase
> should propose a solution which permits to get rid of Zombie
> spid without a need to reboot the server.
> I still would like to know what is the best way to get the
> list of Zombie spids:
> Would the following work in all cases?
> select * from master..syslocks where spid not in (select
> spid from master..sysprocesses)
> but according to Bret the zombie spid is in sysprocesses, I
> am really confused, please shed some light.

That is because you appear to be looking for a list of phantom
spids rather than zombie spids.

zombie spid - exists in sysprocesses, may or may not have
an open transaction, may or may not own any locks. usually
in "sleep" state. kill command does not kill it. (examining
the PSS structure with dbcc pss() will show the "KILLYOUSELF"
flag turned on, which is really all that the kill command
does).

phantom spid - does not exist in sysprocesses, but spid shows
up in syslocks, systransactions, or syslogshold.

Your form of query will work find for detecting these
phantom spids. syslocks is by far the most common
place to see them.

There are yet other related situations that are similar to
phantom spids, but there is no longer any spid stored anywhere.
These are related to "keep counts" in structures that keep track
of how many spids are using the structure.
For instance, you may not be able to drop a database,
load a database, or set it into single user mode
because ASE thinks it is in use. It may
be that it is actually in use, but it is also possible that
the counter of current users didn't get decremented when some
process died unexpectedly.

Something similar can also be seen at the table level -
ASE may incorrectly think the table is actively being used in
general or that a REORG or isolation level 0 scan is occurring.