SQL Server SNAPSHOT Isolation mode in FILESTREAM database on SQL Server 2008

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 TwitterFollow me on FaceBook

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.