up vote 11 down vote favorite
share [g+] share [fb]

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.

link|improve this question
feedback

2 Answers

up vote 2 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

link|improve this answer
3  
wow, is the PS really necessary? – Nick Kavadias Feb 26 '10 at 14:27
feedback
up vote 11 down vote

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'
link|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.database*s* 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
feedback

Your Answer

 
or
required, but never shown
discard

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