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.
5 Answers
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'
- 10,758
- 7
- 36
- 47
-
3No, 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
-
1as 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
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
- 271
- 2
- 3
right click on the databases directory in the object explorer and start powershell.
type:
get-childitem|select name, snapshotisolationstate
and press return
- 419
- 3
- 12
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'
-
2This answer is for read_committed_snapshot, not for Snapshot Isolation Level like the OP requested. They are two different things. – Kevin Kalitowski Jul 09 '16 at 13:02
How to test whether the snapshot transaction isolation level is enabled
To test whether the snapshot transaction isolation level is enabled, follow these steps:
- Start SQL Server Profiler.
- Create a new trace to connect to the data source that you specified in the Analysis Services project.
- In the Trace Properties dialog box, click the Events Selection tab.
- In the TransactionID column, click to select the check boxes in the row for the SQL:BatchCompleted event and in the row for the SQL:BatchStarting event.
Note To display the TransactionID column, click to select the Show all columns check box.
- Click Run to start the trace.
In Business Intelligence Development Studio, process the Analysis Services project.
In SQL Server Profiler, look for the SQL:BatchCompleted events and for the SQL:BatchStarting events that have the same value in the TransactionID column. Typically, these events contain the SELECT statement in the TextData column. For these events, obtain the session ID in the SPID column.
To connect to the data source,start SQL Server Management Studio.
Create a new query, and then run the following Transact-SQL statement.
select session_id,Transaction_Isolation_Level from sys.dm_exec_sessions where session_id=
Note In this statement, is a placeholder for the session ID that you obtained in step 7.
- On the Results tab, note the value in the Transaction_Isolation_Level column. This value indicates the transaction isolation level that you are using in the Analysis Services project. When the snapshot transaction isolation level is enabled, the value in the Transaction_Isolation_Level column is 5.
The following table shows the values in the Transaction_Isolation_Level column and the corresponding transaction isolation levels.
- 101
- 2