Lock DB


Query very usefull for understand process lock on DB… :-)

SELECT r.session_id as spid, r.[status], r.command, r.blocking_session_id as [blocked by], r.start_time, [Session_Duration_Seconds] = Datediff(ss, (SELECT start_time FROM sys.dm_exec_requests WHERE r.session_id = session_id), Getdate())

,DB_NAME(r.database_id) AS DB_Name,r.wait_type, s.login_name, s.host_name, CAST(replace(replace(replace(t.text,char(9),' '),char(10),' '),char(13),' ') AS varchar(4000))
FROM sys.dm_exec_requests AS r
LEFT OUTER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.session_id <> @@SPID AND r.session_id > 50 -- and command <> ''BACKUP DATABASE'' and command <> ''RESTORE DATABASE''
ORDER BY start_time desc