DataBase Locking and UserId's of Users 1

[nikhilparchure]

We have been facing this production issue of locking which is hampering the performance of the system.

What is required is a query that will query the system tables
and would be able to give the locked tables and which process has been locking the same and by which users

Well I have a vague idea of the table involved but it would be helful if i get a refined query for process locking and processes, users locking the databases...

some tables ...
syslocks
sysprocesses
sysdatabases
sysobjects


I have been able to get a very raw query which need to be further refined
Help need guys

Select A.id 'Table ID' ,
B.Name 'TableName' ,
C.hostname 'HostName' ,
C.Program_name 'Program'
from master..syslocks A ,
sysobjects B ,
master..sysprocesses C
Where A.id = B.id
and A.spid = C.spid
and B.type = 'U'

I want the above query to be refined

Regards
Nikhil Parchure




Thanks in Advance

 

[sybaseguru]

Try this procedure. You can create it anywhere you like. The best place is in sybsystemprocs. Good luck

use sybsystemprocs
go
drop proc sp__lock
go
create procedure sp__lock( @dbname char(30)=null,@spid smallint=null )
as
begin
declare @dbid smallint
if @dbname is not null
select @dbid=db_id(@dbname)
if (charindex("sa_role", show_role()) > 0)
begin
select
"Type"=substring(v.name,1,11),
"User"=substring(suser_name(p.suid)+" (pid="+rtrim(convert(char(5),l.spid))+&quot",1,18),
"Table"=substring(db_name(l.dbid)+".."+convert(char(20),object_name(l.id,l.dbid)),1,22),
"Page"=convert(char(8),l.page),
"Cmd"=substring(p.cmd,1,11)
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid
and l.type = v.number
and v.type = "L"
and p.dbid=isnull(@dbid,p.dbid)
and p.spid=isnull(@spid,p.spid)
and l.dbid=isnull(@dbid,l.dbid)
and l.spid=isnull(@spid,l.spid)
order by l.dbid, l.id, v.name
return
end
select
"Type"=substring(v.name,1,11),
"Usernm"=substring(suser_name(p.suid)+" (pid="+rtrim(convert(char(5),l.spid))+&quot",1,18),
"TableNm"=convert(char(22),db_name(l.dbid)+"..&quot,
"Page"=convert(char(8),l.page),
"Cmd"=substring(p.cmd,1,11),
l.id,
l.dbid
into #locks
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid
and l.type = v.number
and v.type = "L"
and l.dbid=isnull(@dbid,l.dbid)
and l.spid=isnull(@spid,l.spid)
and p.dbid=isnull(@dbid,p.dbid)
and p.spid=isnull(@spid,p.spid)
update #locks
set TableNm=TableNm+object_name(id,dbid)
where dbid=db_id() or dbid=1 or dbid=2
update #locks
set TableNm=TableNm+convert(varchar,id)
where dbid<>db_id() and dbid>2
delete #locks
where TableNm like &quot;tempdb..#locks%&quot;
select Type, &quot;User&quot;=Usernm, &quot;Table&quot;=TableNm, Page, Cmd
from #locks
order by dbid, id, Type
return 0
end
go
grant exec on sp__lock to public
go
exit

 

[JeanNiBee]

Very helpful little proc.

Thank you.
-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

TGML reference:

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450&quot;

]