If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > can't kill spid!!! yet it's blocking many others

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 167
can't kill spid!!! yet it's blocking many others

We have a spid 1232 which is causing mass blocking across many other spids, but it doesn't exist and we can't kill it. What do we do? How do we get rid off the goast spid 1232?


fid spid status loginame origname hostname blk_spid dbname cmd block_xloid
------ ------- ------------ ----------- ----------- ----------- ----------- ---------------- ---------------- --------------
0 1 lock sleep INSTAL INSTAL fccmsdsd0 1232 FRNT_PRC_TEST_01 DELETE 0
0 610 lock sleep INSTAL INSTAL 1232 FRNT_PRC_TEST_01 AWAITING COMMAND 0
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 167
Ghost spid... How do we kill it? The "kill" command does't seem to work.

We have a spid "1232" which is causing major blocking. Yet it seems not to exist. We ran the following commands, but not results came back.

use master
go

sp_who '1232'
go

select hostprocess, program_name from sysprocesses where spid = 1232
go

What do we do? How do we stop it from blocking other spids?

Thank you.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: South Africa
Posts: 1,353
No way that I know of besides restarting ASE
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 167
Rebooted ASE to resolve ghost spid that was causing servre blocking...

I just got home from one of the longest days of my life.

Thank you for your feedback and quick response. Honestly... Your feedback made me feel much better about my decision to bounce ASE. I was feeling that I should have known a better way to resolve, but used all the stuff in my bag of tricks and still no success.

We did just that, we ended up just shutting down the ASE server and restarting. The challenge though, we had to do a "shutdown with nowait". A regular shutdown wouldn't work. Luckily for us, all came backup up and is running smoothly now (all databases online, no corruption, etc.).

If you ever come accross a better way to resolve a ghost spid, which is causing catostraphic blocking, please let me know. I'd love to learn.

Thank you.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2007
Posts: 3
From Unix platform the System Administrator can log in as root and kill all user processes thus avoiding ASE server restart with the command

fuser -ku /opt/sybase

(assuming /opt/sybase is the filesystem name housing your sybase)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 86
phantom process?

get rid of all other processes .. logoff users etc ..
dump tran <dbname> with truncate_only

shutdown with no_wait
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On