Update March 5th 2014
-- Removed the CTE and any options that could be incompatible with SQL SERVER 2000
if OBJECT_ID(N'tempdb..#process', N'U') is not null
drop table #process
GO
if OBJECT_ID(N'tempdb..#tmp', N'U') is not null
drop table #tmp
GO
select spid as victim, blocked as blocker
into #process
from master..sysprocesses p1
where spid > 50
and blocked > 0
and spid <> blocked
insert into #process
select spid as victim, blocked as blocker
from master..sysprocesses p
inner join #process
on #process.blocker = p.spid
where spid > 50
select distinct 'dbcc inputbuffer(' + CAST( victim as nvarchar(4)) + ')' as cmd, 'sp_who2 ' + CAST( victim as nvarchar(4)) as spwho, *
into #tmp
from #process
where blocker = 0
order by victim
select *
from #tmp
order by victim
DECLARE @cmd nvarchar(400)
DECLARE @spwho nvarchar(12)
DECLARE curseur CURSOR
FOR
select cmd, spwho
from #tmp
order by victim
OPEN curseur
FETCH NEXT FROM curseur INTO @cmd, @spwho
WHILE @@FETCH_STATUS = 0
BEGIN
print (@cmd)
exec (@cmd)
print (@spwho)
exec (@spwho)
FETCH NEXT FROM curseur INTO @cmd, @spwho
END
CLOSE curseur
DEALLOCATE curseur
GO
select
db_name(dbid),
'dbcc inputbuffer(' + CAST(a.spid as nvarchar(4)) + ')',
a.spid,
a.blocked,
a.status,
a.cmd,
a.cpu,
a.waittime,
a.loginame,
Base = DB_NAME(a.dbid),
*
from master.dbo.sysprocesses a
where 1 = 1
and a.spid > 50
and spid <> @@spid
order by
a.blocked desc,
a.waittime desc,
a.cpu desc
Update 3/6/2013
So we've seen that no sessions are being blocked. Now we can also look at all current locks on your system.
Show locks server wide MSSQL 2000
Monitoring Lock Activity
select convert(varchar(30), suser_sname(p.sid)) as login,
convert (smallint, req_spid) As spid,
convert(varchar(30), db_name(rsc_dbid)) As db_name,
case rsc_dbid when db_id()
then convert(varchar(30), object_name(rsc_objid))
else convert(varchar(30), rsc_objid) end As Object,
rsc_indid As indid,
substring (lock_type.name, 1, 4) As Type,
substring (lock_mode.name, 1, 12) As Mode,
substring (lock_status.name, 1, 5) As Status,
substring (rsc_text, 1, 16) as Resource
from master..syslockinfo s
join master..spt_values lock_type on s.rsc_type = lock_type.number
join master..spt_values lock_status on s.req_status = lock_status.number
join master..spt_values lock_mode on s.req_mode = lock_mode.number -1
join master..sysprocesses p on s.req_spid = p.spid
where lock_type.type = 'LR'
and lock_status.type = 'LS'
and lock_mode.type = 'L'
and db_name(rsc_dbid) not in ('master', 'msdb', 'tempdb', 'model')
order by spid, lock_type.number
go
The output will you tell you
1. what type of lock is being held: database, table, page.
2. the mode of the lock: S shared, X exclusive, RangeS-S where a range of values is locked in an index
Update 3/7/2014
I have updated your screen shot with some basic explanations. What we are seeing, is just a bunch of shared database locks.
To be brief, the locks on your system are shared (in other words, they don't block other sessions from accessing the database) and those locks are meant to prevent the database from being dropped, detached or renamed while data is being read and/or written from and to that database and its contents.
Getting back to your original question; there are no deadlocks or blocked sessions on your server. I don't remember having seen this message before, but from what you are sending back, this is definitely not about deadlocks and more likely to be just an informational message.
Or use sp_lock
sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']
Which will give you a more detailed view of all locks currently being held on your server.
http://technet.microsoft.com/en-us/library/aa238824(v=sql.80).aspx
Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and
request a X lock directly if an update must be performed.
Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of date version. (In the absence of update conflict detection, performing updates based on out-of-date data could result in a "lost update".)
Taking update locks is normal behaviour for a non-row-versioning query that updates data. It is a protection against a common cause of conversion deadlock, but it does not guarantee deadlock avoidance in all cases, especially where a different access path (index) is used to qualify rows to change.
You can find more details about the exact behaviour of RCSI when modifying data in my SQLperformance.com article, "Data Modifications Under Read Committed Snapshot Isolation". There is further background on RCSI in general in the article, "Read Committed Snapshot Isolation".
If the updates really are disjoint, you might consider performing the change using Snapshot Isolation rather than RCSI (which admittedly has complex behaviour in this area).
Best Answer
This is pretty easy to do using the
xml_deadlock_report
in thesystem_health
Extended Event session.After creating some different deadlocks, I get this back: