sybase locks and blocks

here are some basic tips on clearing a block in a database in sybase

log in to the sybase database

isql -S sybasesever -U dbo_user -P sdsdsrere123

1> sp_who
2> go

0 211 recv sleep zbi_trade_dbo zbi_trade_dbo tk0u2314ap 0 apps_gm AWAITING COMMAND 0
0 213 recv sleep read_genfeed read_genfeed ibtkygsw32 0 Clearing AWAITING COMMAND 0
0 218 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0
0 224 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0

you will see a log suspend or you might not you might see a block the next best thing to do is to use the master and select * from syslogshold to see if there are any long running transactions you can also to a sp_lock in the database. the transaction below is doing an insert in this case I would just truncate the log as this is a log suspend as you can see below this has cleaned up the tranlog. If that does not work then you can kill the spid

use master
go
select * from syslogshold
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
31516 0 9 6514 0x721900001600 0x000000000000 Jan 20 2009 9:18AM insert 18

(1 row affected)

dump tran database with truncate_only
go

1> use database
2> go
1> Dbcc checktable(syslogs)
2> go
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 3420.
*** NOTICE: Space used on the log segment is 3470 pages (6.78 MB), 27.11%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 9330 pages (18.22 MB), 72.89%.
Table has 25403 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

if this does not work and the tranlog is still full
kill 9 where 9 is spid
this will then put the transaction in to rollback. Remember to speak to your apps team or dev team I would not just kill the spid.