I started troubleshooting a
tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that
tempdb is out of space. Technically, the error is:
Transaction aborted when accessing versioned row in table 'dbo.inserted' in database 'dbname'. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning
I looked at the database configuration in
sys.databases and found the following settings:
snapshot_isolation_state: 0 snapshot_isolation_state_desc: OFF is_read_committed_snapshot_on: 1
I looked up what this meant in BOL and the salient information is as follows:
State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot isolation state ON. Snapshot isolation is allowed.
2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Cannot start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.
Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
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 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.
If I'm understanding this correctly,
snapshot_isolation_state is OFF and disallowed. However, the 1 on
is_read_committed_snapshot_on indicates that the database is using snapshot isolation. How is this possible if it's disallowed?