Skip to main content
added 284 characters in body
Source Link
Josh Darnell
  • 29.8k
  • 5
  • 67
  • 124

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up. If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT 
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

screenshot of query results showing worktable transaction starting at 04:41:04

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

screenshot of error log messages showing tempdb starting up at 04:41:03

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.


Since you mentioned that you didn't see these in sp_WhoIsActive, it's worth pointing out that system session's are filtered out of that procedures output by default. You can see them by passing an extra parameter:

EXEC sp_WhoIsActive @show_system_spids = 1;

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up. If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT 
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

screenshot of query results showing worktable transaction starting at 04:41:04

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

screenshot of error log messages showing tempdb starting up at 04:41:03

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up. If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT 
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

screenshot of query results showing worktable transaction starting at 04:41:04

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

screenshot of error log messages showing tempdb starting up at 04:41:03

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.


Since you mentioned that you didn't see these in sp_WhoIsActive, it's worth pointing out that system session's are filtered out of that procedures output by default. You can see them by passing an extra parameter:

EXEC sp_WhoIsActive @show_system_spids = 1;
Source Link
Josh Darnell
  • 29.8k
  • 5
  • 67
  • 124

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up. If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT 
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

screenshot of query results showing worktable transaction starting at 04:41:04

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

screenshot of error log messages showing tempdb starting up at 04:41:03

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.