Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 5 down vote accepted

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

share|improve this answer
wow, is the PS really necessary? – Nick Kavadias Feb 26 '10 at 14:27

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'
share|improve this answer
No, not wholly necessary but I'm just starting to learn PS so took a look to see how it could be done and thought I'd share .. ! – Fatherjack Feb 26 '10 at 15:14
as much as i hate to admit it, it is cool – Nick Kavadias Feb 26 '10 at 15:22
to be honest, I'm struggling to see its going to be a lot of use to me, we only have 30ish servers. If I wanted the snapshot isolation from every database on every server then I guess PS might be better than T-SQL.. for now I'm seeing it just as an alternative, I'll use it here and there I expect. – Fatherjack Feb 26 '10 at 18:05
I can't edit because it's less than a six-character change, but for other confused souls who copy and paste the query, note that it should be sys.databases and not sys.database. – Mark Sowul Jul 14 '11 at 16:39
thanks, i've fixed the typo up – Nick Kavadias Jul 15 '11 at 6:55

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
share|improve this answer

Or using T-SQL code:

'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'

share|improve this answer
This answer is for read_committed_snapshot, not for Snapshot Isolation Level like the OP requested. They are two different things. – Kevin Kalitowski Jul 9 at 13:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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