Lock Monitoring - SQL Server 2005

As mentioned in the previous post, the query provided below can be
use to check locking on sql server 2005. Script is Simple , but powerful :)


SELECT spid,
       TEXT                                            AS [query],
       request_mode,
       request_type                                    AS LOCK,
       request_status,
       sys.dm_tran_locks.resource_type                 AS object_type,
       Db_name(sysprocesses.dbid)                      AS dbname,
       cpu,
       memusage,
       physical_io,
       status                                          AS query_status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran,
       Db_name(sys.dm_tran_locks.resource_database_id) AS locked_database_name
FROM   sys.dm_tran_locks,
       sys.sysprocesses
       OUTER APPLY Fn_get_sql(sql_handle)
WHERE  spid > 50
       AND sys.dm_tran_locks.request_session_id = spid
ORDER  BY request_mode DESC