Transaction Related Dynamic Management Views and Functions

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.dm_tran_active_snapshot_database_transactions
• sys.dm_tran_active_transactions
• sys.dm_tran_current_snapshot
• sys.dm_tran_current_transaction
• sys.dm_tran_database_transactions
• sys.dm_tran_locks
• sys.dm_tran_session_transactions
• sys.dm_tran_top_version_generators
• sys.dm_tran_transactions_snapshot
• sys.dm_tran_version_store

sys.dm_tran_active_snapshot_database_transactions

In a SQL Server instance, this dynamic management view returns a virtual table for all active transactions that generate or potentially access row versions. Transactions are included for one or more of the following conditions: When either or both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options are set to ON: There is one row for each transaction that is running under snapshot isolation level, or read-committed isolation level that is using row versioning. There is one row for each transaction that causes a row version to be created in the current database. For example, the transaction generates a row version by updating or deleting a row in the current database. When a trigger is fired, there is one row for the transaction under which the trigger is executing. When an online indexing procedure is running, there is one row for the transaction that is creating the index. When Multiple Active Results Sets (MARS) session is enabled, there is one row for each transaction that is accessing row versions. This dynamic management view does not include system transactions.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_id	•	•	•	•	bigint	Unique identification number assigned for the transaction. The transaction ID is primarily used to identify the transaction in locking operations.
transaction_sequence_num	•	•	•	•	bigint	Transaction sequence number. This is a unique sequence number that is assigned to a transaction when it starts. Transactions that do not generate version records and do not use snapshot scans will not receive a transaction sequence number. For more information, see Understanding Row Versioning-Based Isolation Levels1.
commit_sequence_num	•	•	•	•	bigint	Sequence number that indicates when the transaction finishes (commits or stops). For active transactions, the value is NULL.
is_snapshot	•	•	•	•	int	0 = Is not a snapshot isolation transaction. 1 = Is a snapshot isolation transaction.
session_id	•	•	•	•	int	ID of the session that started the transaction.
first_snapshot_sequence_num	•	•	•	•	bigint	Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0.
max_version_chain_traversed	•	•	•	•	int	Maximum length of the version chain that is traversed to find the transactionally consistent version.
average_version_chain_traversed	•	•	•	•	real	Average number of row versions in the version chains that are traversed.
elapsed_time_seconds	•	•	•	•	bigint	Elapsed time since the transaction obtained its transaction sequence number.

TSQL

Sql 2005
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2008
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2008 R2
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2012
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions

Back to Top

---

sys.dm_tran_active_transactions

Returns information about transactions for the instance of SQL Server.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_id	•	•	•	•	bigint	ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance but not unique across all server instances.
name	•	•	•	•	nvarchar(64)	Transaction name. This is overwritten if the transaction is marked and the marked name replaces the transaction name.
transaction_begin_time	•	•	•	•	datetime	Time that the transaction started.
transaction_type	•	•	•	•	int	Type of transaction. 1 = Read/write transaction 2 = Read-only transaction 3 = System transaction 4 = Distributed transaction
transaction_uow	•	•	•	•	uniqueidentifier	Transaction unit of work (UOW) identifier for distributed transactions. MS DTC uses the UOW identifier to work with the distributed transaction.
transaction_state	•	•	•	•	int	0 = The transaction has not been completely initialized yet. 1 = The transaction has been initialized but has not started. 2 = The transaction is active. 3 = The transaction has ended. This is used for read-only transactions. 4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place. 5 = The transaction is in a prepared state and waiting resolution. 6 = The transaction has been committed. 7 = The transaction is being rolled back. 8 = The transaction has been rolled back.
transaction_status	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
transaction_status2	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
dtc_state	•	•	•	•	int	1 = ACTIVE 2 = PREPARED 3 = COMMITTED 4 = ABORTED 5 = RECOVERED
dtc_status	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
dtc_isolation_level	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
filestream_transaction_id			•	•	varbinary(128)	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TSQL

Sql 2005
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level] FROM sys.dm_tran_active_transactions
Sql 2008
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level] FROM sys.dm_tran_active_transactions
Sql 2008 R2
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level], [filestream_transaction_id] FROM sys.dm_tran_active_transactions
Sql 2012
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level], [filestream_transaction_id] FROM sys.dm_tran_active_transactions

Back to Top

---

sys.dm_tran_current_snapshot

Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts. If the current transaction is not a snapshot transaction, this function returns no rows. sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that sys.dm_tran_current_snapshot returns only the active transactions for the current snapshot transaction.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_sequence_num	•	•	•	•	bigint	Transaction sequence number of the active transaction.

TSQL

Sql 2005
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2008
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2008 R2
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2012
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot

Back to Top

---

sys.dm_tran_current_transaction

Returns a single row that displays the state information of the transaction in the current session.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_id	•	•	•	•	bigint	Transaction ID of the current snapshot.
transaction_sequence_num	•	•	•	•	bigint	Sequence number of the transaction that generates the record version.
transaction_is_snapshot	•	•	•	•	bit	Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0.
first_snapshot_sequence_num	•	•	•	•	bigint	Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0.
last_transaction_sequence_num	•	•	•	•	bigint	Global sequence number. This value represents the last transaction sequence number that was generated by the system.
first_useful_sequence_num	•	•	•	•	bigint	Global sequence number. This value represents the oldest transaction sequence number of the transaction that has row versions that must be retained in the version store. Row versions that were created by prior transactions can be removed.

TSQL

Sql 2005
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2008
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2008 R2
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2012
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction

Back to Top

---

sys.dm_tran_database_transactions

Returns information about transactions at the database level.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_id	•	•	•	•	bigint	ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance, but not unique across all server instances.
database_id	•	•	•	•	int	ID of the database associated with the transaction.
database_transaction_begin_time	•	•	•	•	datetime	Time at which the database became involved in the transaction. Specifically, it is the time of the first log record in the database for the transaction.
database_transaction_type	•	•	•	•	int	1 = Read/write transaction 2 = Read-only transaction 3 = System transaction
database_transaction_state	•	•	•	•	int	1 = The transaction has not been initialized. 3 = The transaction has been initialized but has not generated any log records. 4 = The transaction has generated log records. 5 = The transaction has been prepared. 10 = The transaction has been committed. 11 = The transaction has been rolled back. 12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.
database_transaction_status	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_status2	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_log_record_count	•	•	•	•	int	Number of log records generated in the database for the transaction.
database_transaction_replicate_record_count	•	•	•	•	int	Number of log records generated in the database for the transaction that will be replicated.
database_transaction_log_bytes_used	•	•	•	•	bigint	Number of bytes used so far in the database log for the transaction.
database_transaction_log_bytes_reserved	•	•	•	•	bigint	Number of bytes reserved for use in the database log for the transaction.
database_transaction_log_bytes_used_system	•	•	•	•	int	Number of bytes used so far in the database log for system transactions on behalf of the transaction.
database_transaction_log_bytes_reserved_system	•	•	•	•	int	Number of bytes reserved for use in the database log for system transactions on behalf of the transaction.
database_transaction_begin_lsn	•	•	•	•	numeric(25,0)	Log sequence number (LSN) of the begin record for the transaction in the database log.
database_transaction_last_lsn	•	•	•	•	numeric(25,0)	LSN of the most recently logged record for the transaction in the database log.
database_transaction_most_recent_savepoint_lsn	•	•	•	•	numeric(25,0)	LSN of the most recent savepoint for the transaction in the database log.
database_transaction_commit_lsn	•	•	•	•	numeric(25,0)	LSN of the commit log record for the transaction in the database log.
database_transaction_last_rollback_lsn	•	•	•	•	numeric(25,0)	LSN that was most recently rolled back to. If no rollback has taken place, the value will be MaxLSN (-1:-1:-1).
database_transaction_next_undo_lsn	•	•	•	•	numeric(25,0)	LSN of the next record to undo.

TSQL

Sql 2005
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2008
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2008 R2
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2012
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions

Back to Top

---

sys.dm_tran_locks

Returns information about currently active lock manager resources in SQL Server 2012. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
resource_type	•	•	•	•	nvarchar(120)	Represents the resource type. The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.
resource_subtype	•	•	•	•	nvarchar(120)	Represents a subtype of resource_type. Acquiring a subtype lock without holding a nonsubtyped lock of the parent type is technically valid. Different subtypes do not conflict with each other or with the nonsubtyped parent type. Not all resource types have subtypes.
resource_database_id	•	•	•	•	int	ID of the database under which this resource is scoped. All resources handled by the lock manager are scoped by the database ID.
resource_description	•	•	•	•	nvarchar(512)	Description of the resource that contains only information that is not available from other resource columns.
resource_associated_entity_id	•	•	•	•	bigint	ID of the entity in a database with which a resource is associated. This can be an object ID, Hobt ID, or an Allocation Unit ID, depending on the resource type.
resource_lock_partition	•	•	•	•	int	ID of the lock partition for a partitioned lock resource. The value for nonpartitioned lock resources is 0.
request_mode	•	•	•	•	nvarchar(120)	Mode of the request. For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.
request_type	•	•	•	•	nvarchar(120)	Request type. The value is LOCK.
request_status	•	•	•	•	nvarchar(120)	Current status of this request. Possible values are GRANTED, CONVERT, or WAIT.
request_reference_count	•	•	•	•	smallint	Returns an approximate number of times the same requestor has requested this resource.
request_lifetime	•	•	•	•	int	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
request_session_id	•	•	•	•	int	Session ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.
request_exec_context_id	•	•	•	•	int	Execution context ID of the process that currently owns this request.
request_request_id	•	•	•	•	int	Request ID (batch ID) of the process that currently owns this request. This value will change every time that the active Multiple Active Result Set (MARS) connection for a transaction changes.
request_owner_type	•	•	•	•	nvarchar(120)	Entity type that owns the request. Lock manager requests can be owned by a variety of entities. Possible values are: TRANSACTION = The request is owned by a transaction. CURSOR = The request is owned by a cursor. SESSION = The request is owned by a user session. SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace. EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.
request_owner_id	•	•	•	•	bigint	ID of the specific owner of this request. This value is only used for transactions for which this is the transaction ID.
request_owner_guid	•	•	•	•	uniqueidentifier	GUID of the specific owner of this request. This value is only used by a distributed transaction where the value corresponds to the MS DTC GUID for that transaction.
request_owner_lockspace_id	•	•	•	•	nvarchar(64)	Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. This value represents the lockspace ID of the requestor. The lockspace ID determines whether two requestors are compatible with each other and can be granted locks in modes that would otherwise conflict with one another.
lock_owner_address	•	•	•	•	varbinary(8)	Memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.

TSQL

Sql 2005
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2008
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2008 R2
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2012
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks

Back to Top

---

sys.dm_tran_session_transactions

Returns correlation information for associated transactions and sessions.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
session_id	•	•	•	•	int	ID of the session under which the transaction is running.
transaction_id	•	•	•	•	bigint	ID of the transaction.
transaction_descriptor	•	•	•	•	binary(8)	Transaction identifier used by SQL Server when communicating with the client driver.
enlist_count	•	•	•	•	int	Number of active requests in the session working on the transaction.
is_user_transaction	•	•	•	•	bit	1 = The transaction was initiated by a user request. 0 = System transaction.
is_local	•	•	•	•	bit	1 = Local transaction. 0 = Distributed transaction or an enlisted bound session transaction.
is_enlisted	•	•	•	•	bit	1 = Enlisted distributed transaction. 0 = Not an enlisted distributed transaction.
is_bound	•	•	•	•	bit	1 = The transaction is active on the session via bound sessions. 0 = The transaction is not active on the session via bound sessions.

TSQL

Sql 2005
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2008
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2008 R2
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2012
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions

Back to Top

---

sys.dm_tran_top_version_generators

Returns a virtual table for the objects that are producing the most versions in the version store. sys.dm_tran_top_version_generators returns the top 256 aggregated record lengths that are grouped by the database_id and rowset_id. sys.dm_tran_top_version_generators retrieves data by querying the dm_tran_version_store virtual table. sys.dm_tran_top_version_generators is an inefficient view to run because this view queries the version store, and the version store can be very large. We recommend that you use this function to find the largest consumers of the version store.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
database_id	•	•	•	•	int	Database ID.
rowset_id	•	•	•	•	bigint	Rowset ID.
aggregated_record_length_in_bytes	•	•	•	•	int	Sum of the record lengths for each database_id and rowset_id pair in the version store.

TSQL

Sql 2005
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2008
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2008 R2
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2012
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators

Back to Top

---

sys.dm_tran_transactions_snapshot

Returns a virtual table for the sequence_number of transactions that are active when each snapshot transaction starts. The information that is returned by this view can you help you do the following: Find the number of currently active snapshot transactions. Identify data modifications that are ignored by a particular snapshot transaction. For a transaction that is active when a snapshot transaction starts, all data modifications by that transaction, even after that transaction commits, are ignored by the snapshot transaction. For example, consider the following output from sys.dm_tran_transactions_snapshot: transaction_sequence_num snapshot_id snapshot_sequence_num ------------------------ ----------- --------------------- 59 0 57 59 0 58 60 0 57 60 0 58 60 0 59 60 3 57 60 3 58 60 3 59 60 3 60 The transaction_sequence_num column identifies the transaction sequence (XSN) number of the current snapshot transactions. The output shows two: 59 and 60. The snapshot_sequence_num column identifies the transaction sequence number of the transactions that are active when each snapshot transaction starts. The output shows that snapshot transaction XSN-59 starts while two active transactions, XSN-57 and XSN-58, are running. If XSN-57 or XSN-58 makes data modifications, XSN-59 ignores the changes and uses row versioning to maintain a transactionally consistent view of the database. Snapshot transaction XSN-60 ignores data modifications made by XSN-57 and XSN-58 and also XSN 59.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_sequence_num	•	•	•	•	bigint	Transaction sequence number (XSN) of a snapshot transaction.
snapshot_id	•	•	•	•	int	Snapshot ID for each Transact-SQL statement started under read-committed using row versioning. This value is used to generate a transactionally consistent view of the database supporting each query that is being run under read-committed using row versioning.
snapshot_sequence_num	•	•	•	•	bigint	Transaction sequence number of a transaction that was active when the snapshot transaction started.

TSQL

Sql 2005
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2008
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2008 R2
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2012
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot

Back to Top

---

sys.dm_tran_version_store

Returns a virtual table that displays all version records in the version store. sys.dm_tran_version_store is inefficient to run because it queries the entire version store, and the version store can be very large. Each versioned record is stored as binary data together with some tracking or status information. Similar to records in database tables, version-store records are stored in 8192-byte pages. If a record exceeds 8192 bytes, the record will be split across two different records. Because the versioned record is stored as binary, there are no problems with different collations from different databases. Use sys.dm_tran_version_store to find the previous versions of the rows in binary representation as they exist in the version store.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
transaction_sequence_num	•	•	•	•	bigint	Sequence number of the transaction that generates the record version.
version_sequence_num	•	•	•	•	bigint	Version record sequence number. This value is unique within the version-generating transaction.
database_id	•	•	•	•	int	Database ID of the versioned record.
rowset_id	•	•	•	•	bigint	Rowset ID of the record.
status	•	•	•	•	tinyint	Indicates whether a versioned record has been split across two records. If the value is 0, the record is stored in one page. If the value is 1, the record is split into two records that are stored on two different pages.
min_length_in_bytes	•	•	•	•	smallint	Minimum length of the record in bytes.
record_length_first_part_in_bytes	•	•	•	•	smallint	Length of the first part of the versioned record in bytes.
record_image_first_part	•	•	•	•	varbinary(8000)	Binary image of the first part of version record.
record_length_second_part_in_bytes	•	•	•	•	smallint	Length of the second part of version record in bytes.
record_image_second_part	•	•	•	•	varbinary(8000)	Binary image of the second part of the version record.

TSQL

Sql 2005
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2008
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2008 R2
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2012
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store

Back to Top

---