December 6, 2012

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

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

4 comments:

  1. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, Single Row Function in sql

    ReplyDelete
    Replies
    1. Glad that you found it useful, thats what its all about ;)

      Delete
  2. nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Single Row Function



    ReplyDelete
    Replies
    1. Once again, Glad that you found it useful, thanks alot ;)

      Delete

Total Pageviews