Collect Lock information

USE [msdb]
GO

/****** Object:  Job [Collect Lock information]    Script Date: 08/16/2012 11:30:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/16/2012 11:30:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect Lock information',
        @enabled=0,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'DBA_ONLY', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run tsql]    Script Date: 08/16/2012 11:30:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run tsql',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE [SysAdmin]
GO

/****** Object:  Table [dbo].[locks]    Script Date: 10/22/2010 11:23:14 ******/
IF  not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[locks]'') AND type in (N''U''))


CREATE TABLE [dbo].[locks](
    [databasename] [nvarchar](128) NULL,
    [resource_type] [nvarchar](60) NOT NULL,
    [requested_object_name] [nvarchar](128) NULL,
    [lock_type] [nvarchar](60) NOT NULL,
    [request_status] [nvarchar](60) NOT NULL,
    [request_session_id] [int] NOT NULL,
    [lock_owner_address] [varbinary](8) NOT NULL,
    [request_owner_id] [bigint] NULL,
    [TimeofOccurence] [datetime] NOT NULL
) ON [PRIMARY]


use master
go

DECLARE @COUNTER int
, @RUN_TIME_MIN int
, @STOPAT int
   
SET @COUNTER=0;


truncate table SysAdmin..locks

WHILE 1=1

BEGIN

with all_locks (databasename,resource_type, requested_object_name,lock_type,request_status,request_session_id,
          lock_owner_address,request_owner_id,TimeofOccurence)
as
(
select Db_name(resource_database_id) databasename,
       resource_type,
       ( case
           when resource_type = ''OBJECT'' then Object_name(resource_associated_entity_id, resource_database_id)
           when resource_type in ( ''DATABASE'', ''FILE'', ''METADATA'' ) then ''N/A''
           when resource_type in ( ''KEY'', ''PAGE'', ''RID'' ) then (select Object_name(object_id)
                                                                from   sys.partitions
                                                                where  hobt_id = resource_associated_entity_id)
           else ''Undefined''
         end )                       as requested_object_name,
       request_mode                  as lock_type,
       request_status,
       request_session_id,
       lock_owner_address,
       request_owner_id              as transaction_id,
       getdate() as TimeofOccurence
from   sys.dm_tran_locks
where  resource_type not in ( ''DATABASE'', ''METADATA'', ''APPLICATION'' )
      
)


insert into sysadmin..locks
select * from all_locks
where lock_type =''X''



SET @COUNTER=@COUNTER+1;
WAITFOR DELAY ''00:00:01'';

IF @COUNTER = -1 BREAK;

END
',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO