In SQL Server 2005/2008, how can I tell if Snapshot Isolation is turned on? I know how to turn it on, but I can't find the incantation to get google to tell me how to query the state of the Snapshot Isolation option.
right click on the databases directory in the object explorer and start powershell.
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'