My database contains server "lock sleep" for over a day and I look for sp_who and sysprocesses and found out that come from cron job dump database script. I would like to know if "lock sleep" an O.K for database server? Does the blocking spid display under sp_who blk column? How to identify the blocking need to be kill? should I kill the process in database level with "kill spid" or kill in unix level "kill pid"? Any other system tables I can get the information for current locking status?
please tell about the root of lock sleep, cause it hangs the process.
the only solution i know is to kill the process of specific spid that block the process of another spid which is indicated at blk_spid
Killing the pid at the Unix level should result in the ASE spid dying (tho' not always). In your case, at the Unix level, the pid is most likely for a sybmultbuf process; very occasionally these do hang.
> Any other system tables I can get the information for current locking
The standard Sybase sp_lock will give you detailed lock information.
If you've got the MDA (monitoring) tables installed then try the following SP's for helping to analyse blocking: sp__mon_sql2 (run sp__mon_sql2 1 to trace the OS process and other info) sp__block