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