Querying database Locks

To query the root cause of a database lock:

SELECTA.spid,a.blocked,a.waitresource,a.dbid,a.hostname,a.program_name,c.text  asA_sql, B.hostname asB_hostname,b.program_name asB_programname,d.text  asB_sql fromsys.sysprocesses aINNER JOINSys.sysprocesses b onB.spid=a.blocked CrossAPPLY sys.dm_exec_sql_text (a.sql_handle) C CrossAPPLY sys.dm_exec_sql_text (b.sql_handle) dWHEREA.blocked<>0

Check the database for all the locked SPID

SELECT *  from WHERE a.blocked<>0

Check the database ID and name mappings

-- according to the name check ID SELECT db_id ('users') -- check name by ID SELECT *  from WHERE database_id=

Querying database Locks