May 7, 2009 at 4:49 pm
Hi in SQL 2005 you could run activity window and then see lock processes and by object
Where is this in SQL 2008 i see the activity windown but not a away to see what TABLE is locked ?
May 7, 2009 at 8:57 pm
It doesnt look like that feature got carried forward. I ran a trace with profiler to get the script and the same script seems to work in 2008. I have altered it a little so you can create a stored procedure if you want the same functionality in 2008.
Create Procedure LocksByObject
AS
-- cleanup
if (object_id(N'tempdb..#snapshot_processinfo') is not null)
exec(N'drop table #snapshot_processinfo')
if (object_id(N'tempdb..#snapshot_lockinfo' ) is not null)
exec(N'drop table #snapshot_lockinfo' )
if (object_id(N'tempdb..#snapshot_processinfofltr') is not null)
exec(N'drop table #snapshot_processinfofltr')
if (object_id(N'tempdb..#snapshot_locks_perobject_sortedindex') is not null)
exec(N'drop table #snapshot_locks_perobject_sortedindex')
if (object_id(N'tempdb..#Temp') is not null)
exec(N'drop table #Temp')
-- build snapshot containing info about processes #snapshot_processinfo
select
[Process ID] = p.spid,
[IsSystemProcess] = case when s.is_user_process = 1 then 0 else 1 end,
[User] = p.loginame, [Database] = ISNULL(db_name(p.dbid),N''),
[Status] = p.status,
[Open Transactions] = p.open_tran,
[Command] = p.cmd,
[Application] = p.program_name,
[Wait Time] = p.waittime,
[Wait Type] = case when p.waittype = 0
then N''
else p.lastwaittype
end,
[Wait Resource] = case when p.waittype = 0
then N''
else p.waitresource
end,
[CPU] = p.cpu,
[Physical IO] = p.physical_io,
[Memory Usage] = p.memusage,
[Login Time] = p.login_time,
[Last Batch] = p.last_batch,
[Host] = p.hostname,
[Net Library] = p.net_library,
[Net Address] = p.net_address,
[Blocked By] = p.blocked,
[Blocking] = 0,
[Execution Context ID] = p.ecid,
[ID] = IDENTITY(int, 1, 1),
[ROW ID] = 0
into #snapshot_processinfo
from master.dbo.sysprocesses p, master.sys.dm_exec_sessions s
with (NOLOCK)
where p.spid = s.session_id
order by p.spid
update #snapshot_processinfo
set [ROW ID] = [ID]
-- build snapshot containing info about locks (9.0 metadata) (#snapshot_lockinfo)
select
[Database] = ISNULL(db_name(resource_database_id),N'(internal db)') ,
[Schema] = Convert(nvarchar(386),N'') ,
[Object] = Convert(nvarchar(400),N'(internal)') ,
*
into #snapshot_lockinfo
from sys.dm_tran_locks l
with (NOLOCK)
where resource_database_id not in ( select resource_database_id from sys.dm_tran_locks where resource_subtype = 'BULKOP_BACKUP_DB' union select database_id from sys.databases where has_dbaccess(name)=0) order by l.request_session_id
-- determine available databases
--select distinct quotename([Database],N'[')
--from #snapshot_lockinfo
--where db_name(resource_database_id) is not null
update #snapshot_lockinfo set
[Schema] = schema_name(z.schema_id)
from sys.tables z
where
resource_type = N'TAB'
AND resource_associated_entity_id = z.object_id
AND quotename([Database],N'[') = N'[tempdb]'
update #snapshot_lockinfo set
[Object] = [Database] + N'.' + [Schema] + N'.' + ISNULL(object_name(resource_associated_entity_id),N'(internal table)')
where
resource_type = N'TAB'
AND resource_associated_entity_id between 0 and 2147483647
AND quotename([Database],N'[') = N'[tempdb]'
update #snapshot_lockinfo set
[Object] = [Database] + N'.' + [Schema] + N'.' + ISNULL(object_name(p.object_id),N'(internal heap/btree)')
from sys.partitions p
where
(resource_type = N'HBT' OR resource_type = N'PAG' OR resource_type = N'KEY' OR resource_type = N'RID' )
AND p.partition_id = resource_associated_entity_id
AND p.object_id between 0 and 2147483647
AND quotename([Database],N'[') = N'[tempdb]'
-- update tables with computed info (9.0 metadata) #snapshot_processinfo:processes #snapshot_lockinfo:locks
update #snapshot_processinfo
set [Blocking] = 1
where [Process ID] in (select [Blocked By] from #snapshot_processinfo where [Blocked By] > 0)
update #snapshot_lockinfo
set [Object] = N'allocation unit (' + resource_type + N')'
where resource_type = N'AU' OR resource_type = N'EXT'
update #snapshot_lockinfo
set [Object] = N'file'
where resource_type = N'FIL'
update #snapshot_lockinfo
set [Object] = 'application'
where resource_type = N'APP'
update #snapshot_lockinfo
set [Object] = N'metadata'
where resource_type = N'MD'
update #snapshot_lockinfo
set [Object] = ISNULL(db_name(resource_database_id),N'(internal db)')
where resource_type = N'DB'
-- generate the table containing filtered processes
select * into #snapshot_processinfofltr from #snapshot_processinfo where [Process ID] > 50
-- generate info required to display locks per process (9.0 metadata)
if (object_id('#snapshot_locks_perobject') is not null)
drop table #snapshot_locks_perobject
select request_session_id,request_exec_context_id,request_request_id,resource_type,resource_subtype,resource_associated_entity_id,resource_description,request_mode,request_type,request_status,request_owner_type,request_owner_id,request_owner_guid,[Database],[Object],[ID] = IDENTITY(int, 1, 1),
[ROW ID] = 0
into #snapshot_locks_perobject from #snapshot_lockinfo l
order by l.[Object]
update #snapshot_locks_perobject set [ROW ID] = [ID]
-- page grid storage: new sort order
-- build auxiliary table: #snapshot_locks_perobject_sortedindex
-- sorting field: request_session_id
-- source: #snapshot_locks_perobject
-- where: where [Object] = N'(internal)'
-- order by: order by [request_session_id] ASC
select [request_session_id] = p.[request_session_id],
[ROW ID] = p.[ROW ID]
into #Temp from #snapshot_locks_perobject p
where [Object] = N'(internal)'
order by [request_session_id] ASC
select [request_session_id] = l.[request_session_id],
[ROW ID] = l.[ROW ID],
[Range] = IDENTITY (int, 0, 1)
into #snapshot_locks_perobject_sortedindex from #Temp l
order by [request_session_id] ASC
drop table #Temp
-- page grid storage: get range [0,12]
select l.* from #snapshot_locks_perobject l, #snapshot_locks_perobject_sortedindex p
where p.[Range] >= 0 and p.[Range] <= 12 and p.[ROW ID] = l.[ROW ID]
order by p.[Range]
go
May 8, 2009 at 8:56 am
Thanks for script - is there a way to get the TABLE that is locked also i see object and ID but get internal in OBJECT
October 17, 2013 at 11:53 am
I identified the same query at my database. Do you know from where come this query?
I saw that this query is running with my user (sa), at local server and from management studio, but I could not identify which process is calling this query... 🙁
October 17, 2013 at 2:17 pm
I found out... This query is comming from activity monitor. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply