>>Script Language and Platform: Sybase Transact SQL
This stored procedure displays useful information about the transaction of a given spid (client connection).
Author: jflebon
use sybsystemprocs
goif exists (select name from sybsystemprocs..sysobjects
where name=”sp_dba_spidinfo” and type=”P”)
begin
drop procedure sp_dba_spidinfo
end
gocreate procedure sp_dba_spidinfo @sybpid smallint = NULL
as/*
*@(#) Author : JF LEBON
*@(#) Version : 1.0
*@(#) Date : 28/03/2004
*@(#) Syntax : exec sp_dba_spidinfo <sybase process id>
*@(#) Comments :
*@(#) Changes :*/
set nocount on
— Nobody but SA !
if suser_id() !=1
begin
print “WARNING: You have to be SA to execute this procedure.”
return (1)
end— Never use it inside a transaction !
if @@trancount > 0
begin
print “WARNING: You can NOT use this procedure inside a transaction.”
return (1)
end— Few variables…
declare @login varchar(30),
@hostpid varchar(8),
@cmd varchar(16),
@status varchar(12),
@cpu varchar(4),
@mem varchar(4),
@db varchar(30),
@blok varchar(8),
@io varchar(4),
@proc varchar(30),
@line varchar(8),
@tran varchar(64),
@startdate varchar(20),
@stmnt varchar(30),
@logpage varchar(30),
@bloke smallint,
@prog varchar(16),
@host varchar(10)— Enjoy your flight !
if @sybpid is NULL
begin
select spid, “user”=convert(varchar(30),suser_name(suid)),
hostprocess,
“command”=convert(varchar(16),cmd),
“status”=convert(varchar(12),status),
cpu,memusage,physical_io,
“database”=convert(varchar(30),db_name(dbid)),
blocked
from master..sysprocesses
where suser_name(suid) is not NULL
return (0)
end
else
begin
print “” print “”
print ” ==> CONNECTION INFOS – SYBASE PROCESS ID: %1!”,@sybpid
print “”
select @login=convert(varchar(30),suser_name(suid)),
@hostpid=convert(varchar(8),hostprocess),
@cmd=convert(varchar(16),cmd),
@status=convert(varchar(12),status),
@cpu=convert(varchar(8),cpu),
@mem=convert(varchar(4),memusage),
@io=convert(varchar(30),physical_io),
@host=convert(varchar(10),hostname),
@db=convert(varchar(30),db_name(dbid)),
@bloke=blocked,
@prog=convert(varchar(16),program_name)
from master..sysprocesses
where suser_name(suid) is not NULL
and spid = @sybpidprint ” Login Name : %1!”,@login
print ” From Host : %1!”,@host
print ” Using Program : %1!”,@prog
print ” Unix Process : %1!”,@hostpid
print ” Command : %1!”,@cmd
print ” Status : %1!”,@status
print ” CPU Usage : %1!”,@cpu
print ” Memory Usage : %1!”,@mem
print ” I/O Performed : %1!”,@io
print ” Database Name : %1!”,@db
if @bloke <> 0
begin
select @blok=convert(varchar(8),@bloke)
print ” Blocked by spid : %1!”,@blok
end
print “”
print “”
print ” ==> TRANSACTION ANALYSIS – SYBASE PROCESS ID: %1!”,@sybpid
print “”
select @proc=convert(varchar(30),object_name(a.id)),
@line=convert(varchar(8),a.linenum),
@tran=convert(varchar(64),a.tran_name),
@startdate=convert(varchar(20),i.starttime,100),
@db=convert(varchar(30),db_name(i.dbid)),
@logpage=convert(varchar(30),i.page),
@stmnt=convert(varchar(30),stmtnum)
from master..sysprocesses a, master..syslogshold i
where a.spid = @sybpid
and i.spid = @sybpidif @logpage is not null
begin
if @proc is not null
begin
print ” Procedure Name : %1!”,@proc
print ” Procedure Line Number : %1!”,@line
end
print ” Transaction Name : %1!”,@tran
print ” Started On : %1!”,@startdate
print ” Working in Database : %1!”,@db
print ” Current Statement : %1!”,@stmnt
print ” Starting Syslogs Page Number : %1!”,@logpage
end
else
begin print ” NO TRANSACTION IS PERFORMED HERE !!” endprint “” print “”
print ” ==> SHOWPLAN OUTPUT – SYBASE PROCESS ID: %1!”,@sybpid
exec sp_showplan @sybpid,null,null,null
print “” print “”
end
go
Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…
Disclaimer Continued
Back to Database Journal Home
