--=====================================================

--查看当前运行事务

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id, es.login_name, es.host_name, est.text

, cn.last_read, cn.last_write, es.program_name

FROM sys.dm_exec_sessions es

INNER JOIN sys.dm_tran_session_transactions st

ON es.session_id = st.session_id

INNER JOIN sys.dm_exec_connections cn

ON es.session_id = cn.session_id

CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est

LEFT OUTER JOIN sys.dm_exec_requests er

ON st.session_id = er.session_id

AND er.session_id IS NULL        

--=====================================================

--使用SP_WHO和SP_WHO2来查看回话和阻塞

--=====================================================

EXEC SP_WHO

--=====================================================

EXEC SP_WHO2

--=====================================================

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.net_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

--====================================================

--使用SP_LOCK来获取锁

--查找某个对象上的锁

DECLARE @T TABLE

(

 SPID BIGINT,

 DataBaseID INT,

 OBJECTID BIGINT,

 IndexID BIGINT,

 LockType VARCHAR(20),

 LockResource NVARCHAR(200),

 LockMode NVARCHAR(20),

 LockStats NVARCHAR(200)

)

INSERT INTO @T

EXEC SP_LOCK

SELECT SPID

,DataBaseID

,DB_NAME(DataBaseID) AS DataBaseName

,OBJECTID

,OBJECT_Name(OBJECTID,DataBaseID) ObjectName

,IndexID

,LockType

,LockResource

,LockMode

,LockStats

FROM @T

--==================================================

--sp_who_lock

USE master

GO

IF EXISTS (SELECT * FROM dbo.sysobjects

     WHERE id = OBJECT_ID(N'[dbo].[sp_who_lock]')

     AND OBJECTPROPERTY(id, N'IsProcedure') = 1

)

DROP PROCEDURE [dbo].[sp_who_lock]

GO

GO

CREATE PROCEDURE sp_who_lock

AS

BEGIN

DECLARE

@spid INT,

@bl INT,

@intTransactionCountOnEntry INT,

@intRowcount INT,

@intCountProperties INT,

@intCounter INT;

CREATE TABLE #tmp_lock_who

(

   id INT IDENTITY(1,1),

   SPID SMALLINT,

   bl SMALLINT

)

IF @@ERROR<>0 RETURN @@ERROR

INSERT INTO #tmp_lock_who(SPID,bl)

SELECT   0 ,blocked

FROM (SELECT * FROM sysprocesses WHERE   blocked>0 ) a

WHERE NOT EXISTS(

   SELECT * FROM

     (

     SELECT * FROM sysprocesses

     WHERE   blocked>0

     ) b

WHERE a.blocked=SPID)

UNION SELECT SPID,blocked

FROM sysprocesses

WHERE   blocked>0

IF @@ERROR<>0 RETURN @@ERROR

-- 找到临时表的记录数

SELECT @intCountProperties = COUNT(*),@intCounter = 1

FROM #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

IF @intCountProperties=0

SELECT '现在没有阻塞和死锁信息' AS MESSAGE

-- 循环开始

WHILE @intCounter <= @intCountProperties

BEGIN

-- 取第一条记录

SELECT @spid = SPID,@bl = bl

FROM #tmp_lock_who WHERE Id = @intCounter

BEGIN

IF @spid =0

BEGIN

   SELECT '引起数据库死锁的是: '

   + CAST(@bl AS VARCHAR(10))

   + '进程号,其执行的SQL语法如下'

END

ELSE

BEGIN

   SELECT '进程号SPID：'+

   CAST(@spid AS VARCHAR(10))+ '被'

   + '进程号SPID：'+ CAST(@bl AS VARCHAR(10))

   +'阻塞,其当前进程执行的SQL语法如下'

END

DBCC INPUTBUFFER (@bl )

END

-- 循环指针下移

SET @intCounter = @intCounter + 1

END

DROP TABLE #tmp_lock_who

RETURN 0

END

EXEC master.dbo.sp_who_lock

--==========================================================

--查看各回话相关的锁

SELECT S.session_id

,DB_NAME(L.resource_database_id) AS resource_database_name

,L.request_type

,L.request_mode

,L.request_status

,L.resource_description

,S.last_request_start_time

,S.last_request_end_time

,S.status

,S.host_name

FROM sys.dm_tran_session_transactions ST

INNER JOIN sys.dm_tran_locks L

ON ST.session_id=L.request_session_id

INNER JOIN sys.dm_exec_sessions S

ON S.session_id=ST.session_id

ORDER BY S.last_request_end_time ASC

--===========================================

--查看数据库上的隐形事务

--由大菠萝(肖磊)提供

SELECT a.session_id,

b.name,

c.client_net_address,

c.client_tcp_port,

b.transaction_begin_time

FROM sys.dm_tran_session_transactions

a JOIN sys.dm_tran_active_transactions b

ON a.transaction_id=b.transaction_id

JOIN sys.dm_exec_connections c(nolock)

ON a.session_id=c.session_id

WHERE b.name='implicit_transaction'

AND a.is_user_transaction=1

ORDER BY b.transaction_begin_time