none
到底哪个用来查询某个数据库下面的活动事务的脚本是正确的? RRS feed

  • 问题

  • use tempdb
    go
    SELECT 
        A.Session_ID AS [Session ID]
       ,B.Transaction_Begin_Time AS [Transaction Begin Time]
       ,DB_NAME(C.Dbid) AS [Database Name]
       ,C.Program_Name AS [Program Name]
       ,C.Loginame AS [Login Name]
      -- ,D.Text AS [SQL Text] 
    FROM Sys.DM_Tran_Session_Transactions A 
    INNER JOIN  Sys.DM_Tran_Active_Transactions B 
        ON A.Transaction_ID=B.Transaction_ID 
    INNER JOIN  Sys.Sysprocesses C 
        ON A.Session_ID=C.Spid 
    --CROSS APPLY SYS.DM_Exec_Sql_Text(C.Sql_Handle) AS D 
    WHERE C.Dbid=DB_ID('TempDB')
         and B.Transaction_State=2 
    ORDER BY Transaction_Begin_Time
      go
      use tempdb
      go
      dbcc opentran()
       go

    下面的查询出来的是85进程,但是上面那个脚本没有返回结果集

    哪个是正确的呢?

      

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2014年8月21日 7:43

答案

  • Not sure what you expect from first query, you can get all sessions in specific db with 'select * from sys.sysprocesses where dbid = db_id ('db_name')'. It gives you open tran count in open_tran column. To find out if process is read or write, you may check it in sys.dm_exec_requests that tells number of reads/writes each process did.
    2014年8月21日 13:06

全部回复

  • 另外,如何区分一个事务是只读还是有写入操作的

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2014年8月21日 8:35
  • Not sure what you expect from first query, you can get all sessions in specific db with 'select * from sys.sysprocesses where dbid = db_id ('db_name')'. It gives you open tran count in open_tran column. To find out if process is read or write, you may check it in sys.dm_exec_requests that tells number of reads/writes each process did.
    2014年8月21日 13:06
  • 我第一个脚本是想查询出TempDB中当前为active transaction的SPid。

    这个脚本自己拼凑出来的。

    我现在怀疑,有点问题。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2014年8月22日 7:47
  • 第一个脚本 能查出来 申请tempdb 空间的一瞬间的spid ,但是 申请完就马上切换了;

    dbcc opentran() 是跟临时表有关的事务的spid

    2014年8月25日 10:00