MSSQL - Checking Your Oldest Active Transaction with DBCC OPENTRAN

DBCC OPENTRAN is one of the DBCC commands used to manage lot of stuff inside SQL Server.

DBCC OPENTRAN helps to identify oldest active transactions that may be preventing log truncation, and it displays information about the following:

• The oldest active transaction.
• The oldest distributed and non-distributed replicated transactions "if any".

DBCC OPENTRAN displays information about the transaction log of a specified database, whether by specifying USE clause before DBCC OPENTRAN or by specifying database name or ID within DBCC OPENTRAN command itself.

-- Syntax

DBCC OPENTRAN

[

    ( [ database_name | database_id | 0 ] )

    { [ WITH TABLERESULTS ]

      [ , [ NO_INFOMSGS ] ]

    }

]

-- Examples

-- This will provide the output in text format.

USE db_name

DBCC OPENTRAN

GO

-- This will provide the output in tabular format, allowing inserting the output in a table or temp table.

USE db_name

DBCC OPENTRAN WITH TABLERESULTS

GO

-- This will provide the output in tabular format, plus hiding infomational messages.

USE db_name

DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS

GO

-- This way of specifying database name in DBCC command itself.

USE master

DBCC OPENTRAN (db_name) WITH TABLERESULTS

-- Inserting DBCC OPENTRAN output to a SQL Server table

-- 1- creating temp table to hold DBCC OPENTRAN data

IF OBJECT_ID('tempdb.dbo.#temp_dbcc_opentran') IS NOT NULL

DROP TABLE #temp_dbcc_opentran

CREATE TABLE #temp_dbcc_opentran

(

  RowName SYSNAME,

  Value sql_variant

)

-- 2- inserting DBCC OPENTRAN values to temp table

INSERT INTO #temp_dbcc_opentran

EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

-- updating column's values

UPDATE #temp_dbcc_opentran

SET RowName = 'SPID' WHERE RowName = 'OLDACT_SPID'

UPDATE #temp_dbcc_opentran

SET RowName = 'UID' WHERE RowName = 'OLDACT_UID'

UPDATE #temp_dbcc_opentran

SET RowName = 'NAME' WHERE RowName = 'OLDACT_NAME'

UPDATE #temp_dbcc_opentran

SET RowName = 'RECOVERYUNITID' WHERE RowName = 'OLDACT_RECOVERYUNITID'

UPDATE #temp_dbcc_opentran

SET RowName = 'LSN' WHERE RowName = 'OLDACT_LSN'

UPDATE #temp_dbcc_opentran

SET RowName = 'STARTTIME' WHERE RowName = 'OLDACT_STARTTIME'

UPDATE #temp_dbcc_opentran

SET RowName = 'SID' WHERE RowName = 'OLDACT_SID'

UPDATE #temp_dbcc_opentran

SET RowName = 'Oldest distributed LSN' WHERE RowName = 'REPL_DIST_OLD_LSN'

UPDATE #temp_dbcc_opentran

SET RowName = 'Oldest non-distributed LSN' WHERE RowName = 'REPL_NONDIST_OLD_LSN'

-- 3- selecting DBCC OPENTRAN data

SELECT *

FROM #temp_dbcc_opentran

-- 4- getting active transaction duration & replication latency "if exists"

-- active transaction duration "in case of no replication latency"

IF EXISTS (SELECT [Value]

FROM #temp_dbcc_opentran

WHERE RowName = 'LSN')

BEGIN

  DECLARE @LSN NVARCHAR(25) = (SELECT CAST(Value as NVARCHAR(128))

  FROM #temp_dbcc_opentran

  WHERE RowName = 'LSN')

  SELECT @LSN AS ActiveTransactionLSN ,

    [Begin Time] AS TransactionBeginTime, GETDATE() AS CurrentTime,

    DATEDIFF(MINUTE, [Begin Time], GETDATE()) AS TransactionLatencyInMinutes

  FROM ::fn_dblog(REPLACE(REPLACE(@LSN,'(',''),')',''),REPLACE(REPLACE(@LSN,'(',''),')',''))

  WHERE [Begin Time] IS NOT NULL

END

-- active transaction duration "in case of replication latency"

IF EXISTS (SELECT [Value]

FROM #temp_dbcc_opentran

WHERE RowName = 'Oldest non-distributed LSN'

  AND [Value] <> '(0:0:0)')

  BEGIN

  DECLARE @RepLSN NVARCHAR(25) = (SELECT CAST(Value as NVARCHAR(128))

  FROM #temp_dbcc_opentran

  WHERE RowName = 'Oldest non-distributed LSN')

  SELECT @RepLSN AS ActiveTransactionLSN ,

    [Begin Time] AS TransactionBeginTime, GETDATE() AS CurrentTime,

    DATEDIFF(MINUTE, [Begin Time], GETDATE()) AS LogReaderLatencyINMinutes

  FROM ::fn_dblog(REPLACE(REPLACE(@RepLSN,'(',''),')',''),REPLACE(REPLACE(@RepLSN,'(',''),')',''))

  WHERE [Begin Time] IS NOT NULL

END

-- 5- getting SPID details "if exists" & replication latency "if exists"

IF EXISTS (SELECT [Value]

FROM #temp_dbcc_opentran

WHERE RowName = 'SPID')

BEGIN

  DECLARE @SPID INT = (SELECT REPLACE(CAST(Value as NVARCHAR(128)),'s','')

  FROM #temp_dbcc_opentran

  WHERE RowName = 'SPID')

  IF EXISTS (SELECT [name]

  FROM master.sys.objects

  WHERE [name] = 'sp_WhoIsActive')

  EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command=1, 

    @get_plans = 1,@get_full_inner_text = 0,@get_task_info=1, 

    @show_sleeping_spids=2, @filter_type='session', @filter= @SPID

  ELSE

  EXEC sp_who2 @SPID

END

ELSE

SELECT 'There is no SPID information to present!!' AS SPID_Information

-- 6- drop table #temp_dbcc_opentran

IF EXISTS(SELECT 1

FROM tempdb.sys.tables

WHERE NAME LIKE '#temp_dbcc_opentran%')

DROP TABLE #temp_dbcc_opentran

GO

We can have different output based on the type of oldest active transaction "whether it is a normal transaction or a replication transaction that being processed by log reader", also in case we have no active transaction then the result set will be empty.

Example for opened active transactions

The using sp_who2 or sp_WhoIsActive we can get more details for the SPID itself.

So, in summary, this is how we can get more insights about the oldest active transaction on our database, its type and also how much latency we have and some details about the SPID for this transaction.