How can I tell if Snapshot Isolation is turned on?

sql-server

right click on the databases directory in the object explorer and start powershell. type:
get-childitem|select name, snapshotisolationstate
and press return

Powershell, really? what's wrong with good ol' fashioned T-SQL?

sys.databases is what you want. It has human readable description columns like snapshot_isolation_state_desc

SELECT snapshot_isolation_state_desc from sys.databases 
where name='adventureworks'

Expanding on nicks response from above..

The following will return information about all of your databases

    select name
        , s.snapshot_isolation_state
        , snapshot_isolation_state_desc
        , is_read_committed_snapshot_on
        , recovery_model
        , recovery_model_desc
        , collation_name
    from sys.databases s

Or using T-SQL code:

SELECT
'Current Isolation State:' [ ],
CASE is_read_committed_snapshot_on
        WHEN 1 THEN 'ON' ELSE 'OFF'
    END AS [Read Committed Snapsot State]
FROM sys.databases
WHERE name = 'MyDatabaseName'

How can I tell if Snapshot Isolation is turned on?

Related

Recent Posts