At my last job, we supported a SQL database with a VB front-end. Portions of the code were riddled with "undocumented features" which had the potential to lock up the entire database, bringing production to a screeching halt. Using sp_who2, we could see figure out which SPID was the ultimate culprit, but getting the DBAs to kill a SPID in production just didn't happen. We couldn't tie the SPID to a specific person because each VB module used its own generic login and the company's naming convention for workstations made no sense, so we were forced to get everyone to log off the system until the primary lock went away in sp_who2.
Thinking that there had to be a better way, I started picking apart various stored procedures. I wrote the code below (borrowing heavily from sp_who2 and sp_lock) and we found it very helpful. We were now able to diagnose the problem in the initial call, telling the user that Ed Brown or Sara Jones or whoever was causing their problem. Very often, the culprit would have left open an inventory update window and gone to lunch or someone in accounting was running a month-end report. It sure beat having to completely shut down production.
The script is pretty generic and runs in SQL 7.0 and SQL 2000. With minimal tinkering, it works in SQL 6.5 as well. If your users are not logging in with Windows authentication, it can be easily modified to look for SQL authentication (although sp_who2 might just do in that case).
Our application would cause locks in the custom database as well as in master and tempdb (which was full of "permanent temp tables", go figure), so I had three copies of this in the same script separated by USE <dbname> statements.
Anyway, here it is. I hope you like it. Let me know if you find it helpful or have suggestions for improvement.
select msp1.spid as Victim,
convert(varchar(14),(convert(varchar(3),master.dbo.syslockinfo.req_spid)) + ' ' + (convert(varchar(10),msp2.nt_username))) as Culprit,
convert(varchar(14),master.dbo.sysdatabases.name) as [DB_Name],
convert(varchar(50),sysobjects.name) as Locked_Item,
substring (v.name, 1, 3) as Type,
substring (u.name, 1, 7) as Mode,
substring (x.name, 1, 5) as Status
from master.dbo.sysprocesses msp1
right outer join master.dbo.sysprocesses msp2
on msp1.blocked = msp2.spid
inner join master.dbo.syslockinfo
on msp2.spid = master.dbo.syslockinfo.req_spid
inner join sysobjects
on master.dbo.syslockinfo.rsc_objid = sysobjects.id
inner join master.dbo.spt_values as v
on master.dbo.syslockinfo.rsc_type = v.number
inner join master.dbo.spt_values as x
on master.dbo.syslockinfo.req_status = x.number
inner join master.dbo.spt_values as u
on master.dbo.syslockinfo.req_mode + 1 = u.number
inner join master.dbo.sysdatabases
on master.dbo.syslockinfo.rsc_dbid = master.dbo.sysdatabases.dbid
where v.type = 'LR'
and x.type = 'LS'
and u.type = 'L'
and master.dbo.syslockinfo.req_spid <> @@spid
order by master.dbo.syslockinfo.req_spid, master.dbo.sysdatabases.name, sysobjects.name, v.name, u.name, x.name
Thinking that there had to be a better way, I started picking apart various stored procedures. I wrote the code below (borrowing heavily from sp_who2 and sp_lock) and we found it very helpful. We were now able to diagnose the problem in the initial call, telling the user that Ed Brown or Sara Jones or whoever was causing their problem. Very often, the culprit would have left open an inventory update window and gone to lunch or someone in accounting was running a month-end report. It sure beat having to completely shut down production.
The script is pretty generic and runs in SQL 7.0 and SQL 2000. With minimal tinkering, it works in SQL 6.5 as well. If your users are not logging in with Windows authentication, it can be easily modified to look for SQL authentication (although sp_who2 might just do in that case).
Our application would cause locks in the custom database as well as in master and tempdb (which was full of "permanent temp tables", go figure), so I had three copies of this in the same script separated by USE <dbname> statements.
Anyway, here it is. I hope you like it. Let me know if you find it helpful or have suggestions for improvement.
select msp1.spid as Victim,
convert(varchar(14),(convert(varchar(3),master.dbo.syslockinfo.req_spid)) + ' ' + (convert(varchar(10),msp2.nt_username))) as Culprit,
convert(varchar(14),master.dbo.sysdatabases.name) as [DB_Name],
convert(varchar(50),sysobjects.name) as Locked_Item,
substring (v.name, 1, 3) as Type,
substring (u.name, 1, 7) as Mode,
substring (x.name, 1, 5) as Status
from master.dbo.sysprocesses msp1
right outer join master.dbo.sysprocesses msp2
on msp1.blocked = msp2.spid
inner join master.dbo.syslockinfo
on msp2.spid = master.dbo.syslockinfo.req_spid
inner join sysobjects
on master.dbo.syslockinfo.rsc_objid = sysobjects.id
inner join master.dbo.spt_values as v
on master.dbo.syslockinfo.rsc_type = v.number
inner join master.dbo.spt_values as x
on master.dbo.syslockinfo.req_status = x.number
inner join master.dbo.spt_values as u
on master.dbo.syslockinfo.req_mode + 1 = u.number
inner join master.dbo.sysdatabases
on master.dbo.syslockinfo.rsc_dbid = master.dbo.sysdatabases.dbid
where v.type = 'LR'
and x.type = 'LS'
and u.type = 'L'
and master.dbo.syslockinfo.req_spid <> @@spid
order by master.dbo.syslockinfo.req_spid, master.dbo.sysdatabases.name, sysobjects.name, v.name, u.name, x.name
