April 29, 2009 at 6:01 am
Hey everyone,
SQL Server sets ALLOW_SNAPSHOT_ISOLATION ON when a database is set to read only, and resets it to OFF when it is returned to read-write. Can anyone offer any information or theories as to why this might be?
I was thinking it might be a new way of implementing the 'lack-of-locks' behaviour we expect on a read only database; however, explicitly setting ALLOW_SNAPSHOT_ISOLATION to OFF when the database is read only still results in the expected minimal level of locking...? Is the engine falling back to the 'old' way of not taking shared locks in a read-only database?
Row versions *are* generated if you do a select in a transaction after SET TRANSACTION ISOLATION LEVEL SNAPSHOT while ALLOW_SNAPSHOT_ISOLATION is ON in a read-only database - as evidenced by the output from sys.dm_tran_active_snapshot_database_transactions and sys.dm_tran_top_version_generators --- but I am unsure as to why.
As a secondary point - does anyone know why one might want to set either of the row-versioning isolation levels in a read-only database?
Demo:
GO
CREATE DATABASE [D58A3261-C61B-4F81-97E3-8F7C9990628B];
GO
USE [master];
GO
SELECT snapshot_isolation_state_desc AS snapshot_at_start FROM sys.databases WHERE name = 'D58A3261-C61B-4F81-97E3-8F7C9990628B';
GO
ALTER DATABASE [D58A3261-C61B-4F81-97E3-8F7C9990628B] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
SELECT snapshot_isolation_state_desc AS snapshot_when_read_only FROM sys.databases WHERE name = 'D58A3261-C61B-4F81-97E3-8F7C9990628B';
GO
ALTER DATABASE [D58A3261-C61B-4F81-97E3-8F7C9990628B] SET READ_WRITE;
GO
SELECT snapshot_isolation_state_desc AS snapshot_when_read_write FROM sys.databases WHERE name = 'D58A3261-C61B-4F81-97E3-8F7C9990628B';
GO
DROP DATABASE [D58A3261-C61B-4F81-97E3-8F7C9990628B];
Cheers,
Paul[/quote]
April 30, 2009 at 4:40 pm
"Heave ho"
Sorry, I cannot help but maybe somebody of the gurus just oversaw...
Greets
Flo
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply