8

In SQL Server, you can find IMPLICIT_TRANSACTIONS value for own session via @@OPTIONS.

How to find the IMPLICIT_TRANSACTIONS value for another session?

This can definitely be done, because SQL Server Profiler shows it.

2 Answers 2

8

There's no built-in way to achieve this.

Vote for a product improvement at Easier identification of Implicit Transactions (archive link until Microsoft migrates User Voice content)

In the meantime, the workarounds are:

  1. Run a server-side trace (deprecated) including the ExistingConnection event class
  2. Use the existing_connection extended event.
0

The name column of the DMV sys.dm_tran_active_transactions contains this info. Below is an example.

+----------------+---------------------------+-------------------------+------------------+-----------------+-------------------+--------------------+---------------------+-----------+------------+---------------------+---------------------------+
| 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 |
+----------------+---------------------------+-------------------------+------------------+-----------------+-------------------+--------------------+---------------------+-----------+------------+---------------------+---------------------------+
|    28320660670 | LobStorageProviderSession | 2023-10-15 14:17:01.090 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|    28472577580 | implicit_transaction      | 2023-10-15 20:21:01.773 |                1 | NULL            |                 2 |                  0 |                 258 |         0 |          0 |                  -1 | NULL                      |
|    28462491383 | user_transaction          | 2023-10-15 19:56:40.923 |                1 | NULL            |                 2 |                  0 |                 258 |         0 |          0 |                  -1 | NULL                      |
|          16757 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|          16762 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|          16764 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|          16766 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|          16771 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|          16776 | worktable                 | 2023-09-25 23:36:51.540 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|    28472578838 | implicit_transaction      | 2023-10-15 20:21:02.017 |                1 | NULL            |                 2 |                  0 |                 258 |         0 |          0 |                 256 | NULL                      |
|    28472578847 | implicit_transaction      | 2023-10-15 20:21:02.017 |                1 | NULL            |                 2 |                  0 |                 258 |         0 |          0 |                 256 | NULL                      |
|    28471459239 | Backup/Restore            | 2023-10-15 20:18:24.440 |                2 | NULL            |                 2 |                  0 |                   0 |         0 |          0 |                   0 | NULL                      |
|    28472205018 | user_transaction          | 2023-10-15 20:20:07.997 |                1 | NULL            |                 2 |                  0 |                 258 |         0 |          0 |                  -1 | NULL                      |
+----------------+---------------------------+-------------------------+------------------+-----------------+-------------------+--------------------+---------------------+-----------+------------+---------------------+---------------------------+
1
  • This is interesting, but the description in the instructions is somewhat vague. And in my case, this field in very rare cases contains the isolation level.
    – Arty
    Oct 25, 2023 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.