sqlserver数据库查询数据库连接情况和什么语句造成死锁

sqlserver数据库查询数据库连接情况和什么语句造成死锁

//查询数据库的链接情况

SELECT

request_session_id spid,

OBJECT_NAME(

resource_associated_entity_id

) tableName

FROM

sys.dm_tran_locks

WHERE

resource_type = 'OBJECT'

SELECT * FROM

[Master].[dbo].[SYSPROCESSES] WHERE [DBID]

IN

(

SELECT

[DBID]

FROM

[Master].[dbo].[SYSDATABASES]

WHERE

NAME='数据库名称'

)

//查询造成数据库死锁的语句

select er.session_id,CAST(csql.text AS varchar(255)) AS CallingSQL

from master.sys.dm_exec_requests er

WITH (NOLOCK)

CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql

where er.session_id =6

====================================

查询当前进程信息：

SELECT SPID = er.session_id ,Status = ses.status ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con-_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 ORDER BY er.blocking_session_id DESC ,er.session_id

查询死锁语句：

select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'

删除死锁： kill 112 go kill 135

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。