What SQL server command to list the user’s transaction Isolation level

Question

Is there any command I can run on sql server to list the user’s Isolation level transaction?

Answer 1

There is a sp_configure that shows the Server Configuration Options. For example:

exec sp_configure

Output example

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
access check cache bucket count     0           65536       0            0
access check cache quota            0           2147483647  0            0
Ad Hoc Distributed Queries          0           1           0            0
affinity I/O mask                   -2147483648 2147483647  0            0
affinity mask                       -2147483648 2147483647  0            0
affinity64 I/O mask                 -2147483648 2147483647  0            0
affinity64 mask                     -2147483648 2147483647  0            0
Agent XPs                           0           1           1            1
allow updates                       0           1           0            0
backup checksum default             0           1           0            0
backup compression default          0           1           1            1
blocked process threshold (s)       0           86400       0            0
c2 audit mode                       0           1           0            0
clr enabled                         0           1           0            0
common criteria compliance enabled  0           1           0            0
contained database authentication   0           1           0            0
cost threshold for parallelism      0           32767       5            5
cross db ownership chaining         0           1           0            0
cursor threshold                    -1          2147483647  -1           -1
Database Mail XPs                   0           1           1            1
default full-text language          0           2147483647  1033         1033
default language                    0           9999        0            0
default trace enabled               0           1           1            1
disallow results from triggers      0           1           0            0
EKM provider enabled                0           1           0            0
filestream access level             0           2           2            2
fill factor (%)                     0           100         0            0
ft crawl bandwidth (max)            0           32767       100          100
ft crawl bandwidth (min)            0           32767       0            0
ft notify bandwidth (max)           0           32767       100          100
ft notify bandwidth (min)           0           32767       0            0
index create memory (KB)            704         2147483647  0            0
in-doubt xact resolution            0           2           0            0
lightweight pooling                 0           1           0            0
locks                               5000        2147483647  0            0
max degree of parallelism           0           32767       1            1
max full-text crawl range           0           256         4            4
max server memory (MB)              128         2147483647  4096         4096
max text repl size (B)              -1          2147483647  65536        65536
max worker threads                  128         65535       0            0
media retention                     0           365         0            0
min memory per query (KB)           512         2147483647  1024         1024
min server memory (MB)              0           2147483647  1024         1024
nested triggers                     0           1           1            1
network packet size (B)             512         32767       4096         4096
Ole Automation Procedures           0           1           1            1
open objects                        0           2147483647  0            0
optimize for ad hoc workloads       0           1           0            0
PH timeout (s)                      1           3600        60           60
precompute rank                     0           1           0            0
priority boost                      0           1           0            0
query governor cost limit           0           2147483647  0            0
query wait (s)                      -1          2147483647  -1           -1
recovery interval (min)             0           32767       0            0
remote access                       0           1           1            1
remote admin connections            0           1           1            1
remote login timeout (s)            0           2147483647  10           10
remote proc trans                   0           1           0            0
remote query timeout (s)            0           2147483647  0            0
Replication XPs                     0           1           0            0
scan for startup procs              0           1           0            0
server trigger recursion            0           1           1            1
set working set size                0           1           0            0
show advanced options               0           1           1            1
SMO and DMO XPs                     0           1           1            1
transform noise words               0           1           0            0
two digit year cutoff               1753        9999        2049         2049
user connections                    0           32767       0            0
user options                        0           32767       0            0
xp_cmdshell                         0           1           1            1

Answer 2

I found this command that lists the settings set for the user, including transaction Isolation level.

DBCC USEROPTIONS