Snapshot Isolation is a crucial feature in SQL Server that provides transaction-level read consistency. When enabled, it allows readers to see a committed version of data as it existed at the start of their transaction, without being blocked by writers.

To verify if Snapshot Isolation is enabled for a specific database, you can query the sys.databases catalog view:

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

The snapshot_isolation_state_desc column will show:

• OFF - Snapshot isolation is disabled
• ON - Snapshot isolation is enabled
• IN_TRANSITION_TO_ON - The database is in the process of enabling snapshot isolation
• IN_TRANSITION_TO_OFF - The database is in the process of disabling snapshot isolation

Here's a complete script to check the status across all databases:

SELECT 
    name AS DatabaseName,
    snapshot_isolation_state_desc AS SnapshotIsolationState,
    is_read_committed_snapshot_on AS ReadCommittedSnapshot
FROM sys.databases
ORDER BY name;

While snapshot isolation is set at the database level, you should also check the server configuration to ensure ALLOW_SNAPSHOT_ISOLATION is set:

SELECT 
    name,
    snapshot_isolation_state,
    snapshot_isolation_state_desc
FROM sys.databases;

For completeness, here's how you would enable snapshot isolation if it's not already on:

ALTER DATABASE YourDatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON;

To see transactions currently using snapshot isolation:

SELECT 
    t.session_id,
    t.transaction_id,
    t.name,
    t.transaction_begin_time,
    t.transaction_type,
    t.transaction_state,
    t.transaction_status
FROM sys.dm_tran_active_transactions t
WHERE t.transaction_type = 4; -- 4 represents snapshot transactions

When snapshot isolation is enabled, SQL Server maintains row versions in tempdb. You can monitor the version store usage:

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS VersionStoreRecords,
    SUM(record_length_in_bytes) AS TotalSizeBytes
FROM sys.dm_tran_version_store
GROUP BY database_id;

If you encounter error 3961 ("Snapshot isolation transaction failed in database because the object was modified by another transaction..."), it typically means the version store couldn't maintain the required row versions. This often occurs when long-running transactions exist alongside snapshot isolation.

-- Check for long-running transactions
SELECT 
    session_id,
    transaction_id,
    transaction_sequence_num,
    elapsed_time_seconds = DATEDIFF(SECOND, transaction_begin_time, GETDATE())
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

When working with SQL Server 2005 or 2008, verifying whether Snapshot Isolation is enabled is crucial before implementing isolation-level-dependent queries or transactions. Here's how to check the database's Snapshot Isolation setting:

SELECT snapshot_isolation_state_desc, 
       is_read_committed_snapshot_on
FROM sys.databases 
WHERE name = 'YourDatabaseName';

The query returns two important columns:

• snapshot_isolation_state_desc: Shows the current state (OFF, ON, IN_TRANSITION_TO_ON, etc.)
• is_read_committed_snapshot_on: Indicates if read-committed snapshot is enabled (1 = true, 0 = false)

You can also use the DATABASEPROPERTYEX function:

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsReadCommittedSnapshotOn') AS RCSI_Status,
       DATABASEPROPERTYEX('YourDatabaseName', 'SnapshotIsolationState') AS SI_Status;

To see all databases and their snapshot isolation settings:

SELECT name, 
       snapshot_isolation_state_desc,
       is_read_committed_snapshot_on
FROM sys.databases;

Here's a complete script you might use in production:

DECLARE @dbname NVARCHAR(128) = DB_NAME();

SELECT 
    @dbname AS DatabaseName,
    snapshot_isolation_state_desc AS SnapshotIsolationState,
    CASE is_read_committed_snapshot_on
        WHEN 1 THEN 'Enabled'
        ELSE 'Disabled'
    END AS ReadCommittedSnapshotStatus
FROM sys.databases
WHERE name = @dbname;

If you're getting unexpected results:

• Ensure you have proper permissions (VIEW DATABASE STATE)
• Check if the database is online
• Verify you're connected to the correct database instance