查找由于“ACTIVE_TRANSACTION”而导致事务日志“tempdb”已满的原因 [重复]

您可以使用此查询获取整个服务器中正在运行的查询的列表，包括它们的进程 ID、它们运行了多长时间、它们在哪个数据库中运行、谁运行它们、等待统计信息、阻塞查询 ID等。我建议将它放在存储过程中，以便您可以根据需要轻松调用它（即使使用它的默认参数），并且您可以使用 SQL 代理作业定期安排它以将结果记录到表中。

DECLARE @Database VARCHAR(100) = NULL 
DECLARE @EntityName VARCHAR(100) = NULL
DECLARE @ExcludeMyQueries BIT = 0
DECLARE @RunningThreadsOnly BIT = 0
DECLARE @LocksOnly BIT = 0
DECLARE @MinDuration INT = 0
DECLARE @OutputMode INT = 0 -- TODO: Setup formally documented modes, and clean up usage below, can even have different kinds of modes like Verbose, vs EmergenciesOnly, etc

DECLARE @ExecutionDate DATETIME = GETDATE();

SELECT 
    SP.HostName,
    SP.SPID,    
    ER.request_id AS RequestId,
    ER.percent_complete AS PercentComplete, 
    DATEDIFF(s, start_time, @ExecutionDate) AS Duration,
    CAST(((DATEDIFF(s, start_time, @ExecutionDate)) / 3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 3600) / 60 AS VARCHAR) + 'min, ' 
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 60) AS VARCHAR) + ' sec' AS RunningTime, 
    CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), ' 
        + CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + 'min, ' 
        + CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS EstimatedTimeRemaining, 
    DATEADD(SECOND, estimated_completion_time/1000, @ExecutionDate) AS EstimatedCompletionDate, 
    ER.Command,
    ER.blocking_session_id AS BlockingSessionId, 
    LastWaitType,  
    SP.[DBID],  
    DB_NAME(SP.[DBID]) AS DbName,       
    --[TEXT] AS EntityText,
    CPU,
    ER.plan_handle AS PlanHandle,
    ER.query_plan_hash AS QueryPlanHash,
    LOGIN_TIME AS LoginTime,
    LOGINAME AS LoginName, 
    SP.[Status],
    [PROGRAM_NAME] AS ProgramName,
    NT_DOMAIN AS NT_Domain, 
    NT_USERNAME AS NT_Username, 
    @@SERVERNAME AS ServerName,
    @ExecutionDate AS ExecutionDate 
INTO #ExecutingQueries
FROM sys.sysprocesses SP  
INNER JOIN sys.dm_exec_requests ER 
    ON sp.spid = ER.session_id 
WHERE --TEXT NOT LIKE N'%spGetRunningQueries%'
    --AND 
    DB_NAME(SP.dbid) NOT IN ('msdb','master','Distribution')    
    AND
    (
        @Database IS NULL
        OR (@Database IS NOT NULL AND @Database = DB_NAME(SP.[DBID]))
    )
    AND
    (
        @ExcludeMyQueries = 0
        OR (@ExcludeMyQueries = 1 AND hostname <> HOST_NAME())
    )   
    AND
    (
        @RunningThreadsOnly = 0
        OR (@RunningThreadsOnly = 1 AND SP.[Status] = 'RUNNABLE')
    )
    AND 
    (
        @LocksOnly = 0
        OR (@LocksOnly = 1 AND ER.blocking_session_id <> 0) -- TODO: Show the source running query that IS the blocking session ID (will need to join / union this in somehow?)
    )
    AND DATEDIFF(s, start_time, @ExecutionDate) >= @MinDuration

-- TODO: Clean this up and DON'T USE SELECT *
IF (@OutputMode = 0) -- Everything mode
BEGIN
    SELECT *
    FROM #ExecutingQueries
END
ELSE IF (@OutputMode = 1) -- Lightweight mode
BEGIN
    SELECT 
        HostName,
        SPID,
        RequestId,
        RunningTime,
        BlockingSessionId,
        LastWaitType,
        DbName,
        LEFT(EntityText, 100) AS EntityText,
        PlanHandle,
        [Status]
    FROM #ExecutingQueries
END

使用它，您可以查看运行时间最长的查询（Duration或RunningTime列），还可以通过查看LastWaitType和/或BlockingSessionId列来了解它们运行如此长时间的原因。

这是我根据 Adam Machanic 的sp_WhoIsActive创建的一个正在进行的工作，我经常使用它来调试 SQL Server 上的问题。

您可能在 tempdb 中有一个活动事务。您可以看到活跃的交易计数。

select @@TRANCOUNT

您还可以检查 tempdb 的内容以找到它。

SELECT * FROM tempdb..sysobjects

我会做的是检查当您收到此错误时正在运行哪些查询/作业，以便准确找到卡住的内容。