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.
