CODEWITHSUNDEEP
c#.netsql-serverstream
chase
chase

Reputation: 1718

How to write large files to SQL Server FILESTREAM?

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;
}

Upvotes: 14

Views: 6317

Answers (2)

user875318
user875318

Reputation: 586

As suggested by some of the comments, the problem is probably a transaction timeout. You could verify this by running SQL Server Profiler and watching for your transaction to be rolled back.

Unless otherwise specified, the machine.config has a default maxTimeout of 10 minutes, which cannot be overridden via code. To increase the max timeout, add the following to the configuration setting of your machine.config

<system.transactions>
  <machineSettings maxTimeout="00:30:00" />
</system.transactions>

Upvotes: 2

dmportella
dmportella

Reputation: 4724

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/

Upvotes: 5

Related Questions