Wednesday, April 2, 2008

write your own sp_lock for SQL Server 2005 using dynamic view

Write your own sp_lock system stored procedure in SQL Server 2005
sys.dm_tran_locks

The new dynamic view sys.dm_tran_locks returns information about current locks in the system. This view returns the same type of information as sp_lock but with a little bit more detail. The magic here is that it is a view, which enables the DBA to easily join it to other tables.

USE MASTER
GO
CREATE PROCEDURE [dbo].[sp_LockDetail]
AS
BEGIN
SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id,
a.text
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE
s.session_id > 50
END

No comments: