Tuesday, January 14, 2020

Issue in .Net accessing SQL FileStream stored in SQL FCI with CSV

Scenario

We have a .Net application which uses SQL as backend below are some more details

Application

  1. The application supports uploading files and it is stored in SQL Server Filestream.
  2. The application uses SqlFileStream API to interact with SQL Filestream
  3. Application is multitenant where each tenant gets its own database
  4. Since there are a lot of databases high availability is achieved with SQL FCI with CSV(Cluster Shared Volume)

Environment

  1. .Net version 4.6
  2. SQL 2016 (SP2-CU8) - 13.0.5426.0
  3. Hosted in Azure VM - Windows 2019
When the .Net SqlFileStream class save data and transaction is completed an exception is thrown saying 'The transaction has aborted'
It works well when accessing files in Filestream from SSMS using transact SQL

Symptom

There is no error when the SqlFileStream completes its operation. But fails on transaction commit. Below is the stack trace of the exception.

Exception


Inner Exception :
The transaction has aborted. 
STACK TRACE: 
at System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx) 
at System.Transactions.CommittableTransaction.Commit() 
at System.Transactions.TransactionScope.InternalDispose() 
at System.Transactions.TransactionScope.Dispose() at 
----
Removed sensitive code
----
Inner Exception :The transaction could not be committed because an error occurred while tyring to flush FILESTREAM data to disk. A file may have been open at commit time or a disk I/O error may have occurred. '\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\3d382d31-8c57-466a-a59c-57fc2e36a3b4\<schema name>\<table name>\FileStreamContent\483D9F8D-DA5A-47ED-AE6E-1F9B47AA865D\VolumeHint-CSVVolume1\728ea182a82949a5b58669091c7b7d53' was one of the one or more files involved. 
ErorrCode: 0xc0000128 
STACK TRACE: 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) 
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) 
at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment)

Root cause

Microosoft was involved and they confirmed its an issue.Unfortunately not in a state to include moe details.

Resolution

No resolution unless the fix is available.

Workaround

Use non-CSV for storing the file stream data. Continue CSV for table data.

https://support.microsoft.com/en-us/help/4488817/fix-filestream-for-file-i-o-access-feature-can-t-be-enabled-when-you-u
https://serverfault.com/questions/684590/can-we-use-filestream-in-case-of-sql-server-cluster

No comments: