SAP Sybase ASE 15.7 Lock Summary



SAP Sybase ASE 15.7 Lock Summary

This non-SAP stored procedure provides a summary snapshot of locks on an ASE dataserver.

It combines:
Query from master..syslogshold
dbcc sqltext of oldest transaction
Query of master..sysprocesses for oldest transaction showing login name
Query of master..syslocks showing table name of locked objects
Query of master..sysprocesses showing login names showing time blocked if applicable


Sample Output

1> sp_locksummary
===================================
Dataserver: WOODLANE
===================================
System Date: Sep 12 2013 12:58:26
===================================

master..syslogshold:
====================

 spid   syslogshold_db                   trans_start_time
 ------ -------------------------------- ----------------------------------------
     11 pushpullsigns                    Sep 12 2013 12:58PM

master..sysprocesses for spid:
==============================

 spid   system_login_name                cmd
 ------ -------------------------------- ----------------------------
     11 nwalker                          UPDATE

running dbcc sqltext for spid shown above:
==========================================

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
SQL Text:  update customers set first_name = "Jim"

Executing procedure: *ss1306183555_0678169458ss*
Subordinate SQL Text: update customers set first_name = @@@V0_VCHAR1
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.


lock summary:
=============

 spid   db_name                          object_id   table_name               locktype
 ------ -------------------------------- ----------- ------------------------ ------------------------------
     11 pushpullsigns                     1264004503 customers                Ex_page
     11 pushpullsigns                     1264004503 customers                Ex_intent
     11 pushpullsigns                     1264004503 customers                Ex_page-blk
     11 pushpullsigns                     1264004503 customers                Update_page
     11 pushpullsigns                     1264004503 customers                Update_page-blk
     15 pushpullsigns                     1264004503 customers                Ex_intent

master..sysprocesses ordered by time_blocked:
=============================================

 spid   system_login_name                this_spid_blocked_by time_blocked_mins cmd
 ------ -------------------------------- -------------------- ----------------- --------------------------------
     15 psmith                                             11                 0 UPDATE


Stored Procedure T-SQL

use sybsystemprocs
go

create proc sp_locksummary
as

/*
#What is blocking what?
#Stored procedure to provide a summary of sybase ASE 15 system locks and blocks
#ASE 15.7
#Alastair Cook 21st August 2013
#Test and modify as required.
*/

set nocount on

declare @spid int
select @spid = spid from master..syslogshold
declare @string_1 varchar(100)
declare @string_2 varchar(100)

print "==================================="
select @string_1="Dataserver: " + @@servername
print @string_1
print "==================================="
select @string_2="System Date: " + convert(char(21),getdate(),116)
print @string_2
print "==================================="

print " "

select name, number into #sptv from master..spt_values where type = "L"
if(@spid != null)
begin -------------------------- begin
print "master..syslogshold:"
print "===================="
print " "

-- When did the oldest transaction start?

if exists ( select 1 from master..syslogshold )
begin
select spid=spid,dbs=ltrim(db_name(dbid)),starttime=starttime
into #slh
from master..syslogshold
select spid,syslogshold_db=substring(dbs,1,16), trans_start_time=substring(starttime,1,20) from #slh
drop table #slh
end

print " "
print "master..sysprocesses for spid:"
print "=============================="
print " "
select spid,system_login_name=substring(suser_name(suid),1,16), cmd=substring(cmd,1,14)
from master..sysprocesses where spid = @spid
-- Running dbcc sqltext on oldest transaction
print " "
print "running dbcc sqltext for spid shown above:"
print "=========================================="
print " "

dbcc traceon(3604)
dbcc sqltext(@spid)
end -------------------------- end
-- I don't want to report on myself so exclude @@spid
select distinct id,db_name=db_name(dbid),spid,type,locktype="xxxxxxxxxxxxxxx",tablename=object_name(id,dbid)
into #locks
from master..syslocks
where spid != @@spid

update #locks set lcks.locktype=sptv.name
from #sptv sptv, #locks lcks
where sptv.number=lcks.type
print " "
print " "
print "lock summary: " print "============="
print " "
select spid,db_name=substring(db_name,1, 16),object_id=id,table_name=substring(tablename,1,12),locktype from #locks order by spid

print " "
print "master..sysprocesses ordered by time_blocked: "
print "============================================= "
print " "
select spid,system_login_name=substring(suser_name(suid),1,16), this_spid_blocked_by=blocked, time_blocked_mins=time_blocked/60, cmd=substring(cmd,1,16)
from master..sysprocesses where suser_name(suid) != null and spid != @spid and spid != @@spid
order by time_blocked desc

print " "
drop table #sptv

if exists ( select 1 from #locks )
begin
drop table #locks
end

go

Test First on your Development System

Sites are welcome to use the above code to provide a summary view of locks and modify as required.

Contact Information

If there are any changes suggested for this page please contact me.

Alastair Cook email:
cookalastair @ btinternet.com

Last update to this page - 6th Sept 2013