in mssql 2000 there's this code
Declare LockObj_Cur CURSOR Fast_FORWARD FOR
Select id, dbId, type
FROM master..SysLocks (NOLOCK)
WHERE spId = @.spId
AND type in (5, 8, 10, 11)
how do i proceed in mssql 2005 so that i can get the same info as given by above piece of code,
i have found out that sys.dm_tran_locks has to be used but i wasnot able to find equivalent of "type" column of syslocks table of mssql 2000 in sys.dm_tran_locks
plz help
According the 2000 BOL, syslocks was renamed after 6.5 to syslockinfo. And the rsc_type column types only went up to 10. Can you state what you are actually looking for?
On my blog, I have the query that I use all of the time (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!906.entry#postcns!80677FB08B3162E4!906):
select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid
But I haven't documented what every column of the dmv's have yet (I am actually working on a project to do so.)
Louis
sql