How to write large files to SQL Server FILESTREAM?

Question

I'm having a problem writing large amounts of data to FILESTREAM column on SQL Server. Specifically, smallish files around 1.5-2GB are handled fine, but when the size reaches 6GB and up, I'm getting intermittent IOException "The handle is invalid" on .CopyTo() towards the end of the transfer.

I've thought about writing the data in chunks, but SQL Server copies the backing file for the field before allowing to append data to it, which destroys the performance for large files completely.

Here's the code:

public long AddFragment (string location , string description = null) 
{
    const string sql = 
        @"insert into [Fragment] ([Description],[Data]) " +
            "values (@description,0x); " +
         "select [Id], [Data].PathName(), " +
             "GET_FILESTREAM_TRANSACTION_CONTEXT() " +
         "from " +
             "[Fragment] " +
         "where " +
             "[Id] = SCOPE_IDENTITY();";

    long id;

    using (var scope = new TransactionScope(
        TransactionScopeOption.Required, 
            new TransactionOptions {
                Timeout = TimeSpan.FromDays(1)
            })) 
    {
        using (var connection = new SqlConnection(m_ConnectionString)) 
        {
            connection.Open();

            byte[] serverTx;
            string serverLocation;

            using (var command = new SqlCommand (sql, connection)) 
            {
                command.Parameters.Add("@description", 
                    SqlDbType.NVarChar).Value = description;

                using (var reader = command.ExecuteReader ()) 
                {
                    reader.Read();
                    id = reader.GetSqlInt64(0).Value;
                    serverLocation = reader.GetSqlString (1).Value;
                    serverTx = reader.GetSqlBinary (2).Value;
                }
            }

            using (var source = new FileStream(location, FileMode.Open, 
                FileAccess.Read, FileShare.Read, 4096, 
                FileOptions.SequentialScan))
            using (var target = new SqlFileStream(serverLocation, 
                serverTx, FileAccess.Write))
            {
                source.CopyTo ( target );
            }
        }

        scope.Complete();
    }

    return id;
}

Answer 1

I would suggest you use BufferedStream Class around your FileStream class.

Also make sure you are setting the WriteTimeOut property on the SqlFileStream class.

Here you find a really good post that explain everything about SqlFileStream http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/