How can I tell if Snapshot Isolation is turned on?
Solution 1:
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'
Solution 2:
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
Solution 3:
right click on the databases directory in the object explorer and start powershell.
type:
get-childitem|select name, snapshotisolationstate
and press return
Solution 4:
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'