Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

FILESTREAM and Snapshot Isolation Mode in SQL 2008

FILESTREAM and Snapshot Isolation mode are incompatible in SQL 2008.

In SQL 2008 R2 however...
In SQL Server 2008 R2, snapshot isolation has been extended to support FILESTREAM data. Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

Why?  Good info here from the original SQL 2008 documentation:
When FILESTREAM data is accessed through the Win32 APIs, only the read-committed isolation level is supported. Transact-SQL access also allows the repeatable-read and serializable isolation levels. Furthermore, using Transact-SQL access, dirty reads are permitted through the read-uncommitted isolation level, or the NOLOCK query hint, but such access will not show in-flight updates of FILESTREAM data.

Sample failure in SQL 2008 (any edition):
use master
Msg 5099, Level 16, State 3, Line 1
ALTER DATABASE failed because the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options cannot be set to ON when a database has FILESTREAM filegroups. To set READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION to ON, you must remove the FILESTREAM filegroups from the database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


Leave a comment on the original post [, opens in a new window]

Loading comments...