跟踪.NET事务，在SQL中

Question

我定义了这样的交易（vb.net）

Dim objTransaction As New Transaction(IsolationLevel.ReadCommitted, "OpenTransaction")

进行过程，然后用

将其关闭

 objTransaction.Commit()
 objTransaction.Dispose()

或

objTransaction.Rollback()

如果有错误。

现在，如果交易打开，我如何在sql中找到" opentransaction"？

我做

SELECT * FROM sys.dm_tran_active_transactions 

，它给出 - 名称=" user_transaction"，而不是" opentransaction"。我如何在sql中找到我给它的名字？

（我有几个用户在网页上做不同的事情，我发现SQL内有一个很长的开放交易，但是无法确定我在代码中给它的名称）。

<</p> <</p>

Answer 1

您可以将SQL Profiler用于此目的。您可以在SQL Management Studio中启动它，然后设置您的过滤器并启动跟踪

检查此链接以获取更多信息

update

您也可以使用此查询来监视交易：

USE [master]
GO
CREATE PROCEDURE [dbo].[sp_who3]
AS
BEGIN
SET
   TRANSACTION isolation level READ uncommitted;
SELECT
   SPID = er.session_id,
   BlkBy = 
   CASE
      WHEN
         lead_blocker = 1 
      THEN
         - 1 
      ELSE
         er.blocking_session_id 
   END
, ElapsedMS = er.total_elapsed_time , CPU = er.cpu_time , IOReads = er.logical_reads + er.reads , IOWrites = er.writes , Executions = ec.execution_count , CommandType = er.command , LastWaitType = er.last_wait_type , ObjectName = Object_schema_name(qt.objectid, dbid) + '.' + Object_name(qt.objectid, qt.dbid) , SQLStatement = Substring ( qt.text, er.statement_start_offset / 2, 
   CASE
      WHEN
         (
            CASE
               WHEN
                  er.statement_end_offset = - 1 
               THEN
                  Len(CONVERT(NVARCHAR(max), qt.text)) * 2 
               ELSE
                  er.statement_end_offset 
            END
            - er.statement_start_offset / 2 
         )
         < 0 
      THEN
         0 
      ELSE
         CASE
            WHEN
               er.statement_end_offset = - 1 
            THEN
               Len(CONVERT(NVARCHAR(max), qt.text)) * 2 
            ELSE
               er.statement_end_offset 
         END
         - er.statement_start_offset / 2 
   END
) , STATUS = ses.status , [Login] = ses.login_name , Host = ses.host_name , DBName = Db_name(er.database_id) , StartTime = er.start_time , Protocol = con.net_transport , transaction_isolation = 
   CASE
      ses.transaction_isolation_level 
      WHEN
         0 
      THEN
         'Unspecified' 
      WHEN
         1 
      THEN
         'Read Uncommitted' 
      WHEN
         2 
      THEN
         'Read Committed' 
      WHEN
         3 
      THEN
         'Repeatable' 
      WHEN
         4 
      THEN
         'Serializable' 
      WHEN
         5 
      THEN
         'Snapshot' 
   END
, ConnectionWrites = con.num_writes , ConnectionReads = con.num_reads , ClientAddress = con.client_net_address , Authentication = con.auth_scheme , DatetimeSnapshot = Getdate() , plan_handle = er.plan_handle 
FROM
   sys.dm_exec_requests er 
   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 OUTER apply sys.Dm_exec_sql_text(er.sql_handle) AS qt OUTER apply ( 
      SELECT
         execution_count = Max(cp.usecounts) 
      FROM
         sys.dm_exec_cached_plans cp 
      WHERE
         cp.plan_handle = er.plan_handle ) ec OUTER apply ( 
         SELECT
            lead_blocker = 1 
         FROM
            master.dbo.sysprocesses sp 
         WHERE
            sp.spid IN 
            (
               SELECT
                  blocked 
               FROM
                  master.dbo.sysprocesses WITH (nolock) 
               WHERE
                  blocked != 0
            )
            AND sp.blocked = 0 
            AND sp.spid = er.session_id ) lb 
         WHERE
            er.sql_handle IS NOT NULL 
            AND er.session_id != @@SPID 
         ORDER BY
            CASE
               WHEN
                  lb.lead_blocker = 1 
               THEN
                  - 1 * 1000 
               ELSE
                  - er.blocking_session_id 
            END
, er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id;
END

并创建一个sp。您可以使用

exec sp_who3

它将为您提供用户所拥有的所有运行交易，您也可以在需要时进行编辑。