Dear Friends,
Last week we have had a situation where one of our colleague(s) was trying to change database isolation to SQL Server SNAPSHOT Isolation in one of the SQL Server 2008 instances for which we has greeted with following error message;
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.
Error message is very much self-explanatory; clearly it states that we cannot turn on READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION on a FILESTREAM database. Out of curiosity I thought of checking MSDN and came across following explanations for SQL Server 2008 R2;
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.
In case you want to know more; I will recommend you to go through the document available on this link , which clearly states that;
Isolation Semantics : The isolation semantics are governed by database engine transaction isolation levels. 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.
So we can conclude that, FILESTREAM and Snapshot Isolation mode incompatible in SQL Server 2008.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
