How to detect READ_COMMITTED_SNAPSHOT is enabled?

Chris Driver picture Chris Driver · Sep 9, 2008 · Viewed 111.9k times · Source

In MS SQL Server is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON;

I cannot find a simple way to detect this in either T-SQL or via the Management Studio's GUI.

TIA

Answer

Galwegian picture Galwegian · Sep 9, 2008
SELECT is_read_committed_snapshot_on FROM sys.databases 
WHERE name= 'YourDatabase'

Return value:

  • 1: READ_COMMITTED_SNAPSHOT option is ON. Read operations under the READ COMMITTED isolation level are based on snapshot scans and do not acquire locks.
  • 0 (default): READ_COMMITTED_SNAPSHOT option is OFF. Read operations under the READ COMMITTED isolation level use Shared (S) locks.