• I would recommend against running a job every 4 seconds. Instead it would be better for you to make a stored procedure and use the WAITFOR DELAY command inside a loop.

    create proc dbo.get_lock_report

    as

    begin

    while 1=1

    begin

    -- sleep for 4 seconds

    waitfor delay '00:00:04'

    SELECT

    spid = Lock.request_session_id,

    HostName = Session.host_name,

    ProgramName = Session.program_name,

    Cmd = Request.Command,

    LoginName = Session.login_name,

    dbid = Lock.resource_database_id,

    DatabaseName = DB_NAME(Lock.resource_database_id),

    Type = Lock.resource_type,

    LockType = Lock.request_type,

    Mode = Lock.request_mode,

    Status = request_status,

    UserTable.Name

    FROM

    sys.dm_tran_locks AS Lock

    LEFT JOIN

    sys.dm_exec_sessions AS Session ON

    Session.session_id = Lock.request_session_id

    LEFT JOIN

    sys.dm_exec_requests AS Request ON

    Request.session_id = Session.session_id

    LEFT JOIN

    sys.tables AS UserTable ON

    UserTable.object_id = Lock.resource_associated_entity_id

    AND

    type = 'U'

    WHERE

    Lock.resource_type = 'OBJECT'

    end

    end

    go

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato