Below is the sql query I'm using to check the locks as a scheduled job to run every 5 minutes.. But I would need to create a new seperate output file for every run every 5 minutes..

BCP errors that the query is too long

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'

go

Please help.. thanks a lot