March 28, 2006 at 12:05 pm
I have a large app with over 1500 active connections - locking is becoming an issue - in an effort to track down the locks, I wrote a query which will give me the user and application causing the lock - a few of questions:
1. Is the query correct?
2. How can I link in the table affected by the lock?
3. I intend to turn this into a stored procedure - is there a way to write the results out to a file in the SP?
declare @counter integer
set @counter = 1
while @counter < 30
begin
select a.name, c.loginame, b.type, b.mode, c.cpu, c.memusage, c.program_name
from master..sysdatabases a
join (select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
substring (v.name, 1, 4) As Type,
substring (u.name, 1, 8) As Mode
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L')b on a.dbid = b.dbid
join (select spid, blocked, waittime, cpu, physical_io,
memusage, program_name, loginame from master..sysprocesses) c on b.spid = c.spid
where a.dbid <> 11 and a.dbid <> 1 and a.dbid <> 2 and a.dbid <> 4 and a.dbid <> 18
and (b.mode = 'x' or b.mode = 'ix')
order by a.dbid
set @counter = @counter + 1
end
Thanks,
Harley
March 28, 2006 at 12:26 pm
Microsoft has a a tool that does this already:
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
You look at the output using a ms tool called sherlok:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49847
This can be scheduled and the output is easy to follow.
March 28, 2006 at 5:42 pm
Andrew,
Thanks for the response and the links!
Harley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply