Long strings truncated at insertion time

Question

I am running a .NET 4.0 application that communicates using LINQ to SQL with SQL Server 2008 R2 Standard edition. I have a table with a column of type nvarchar(max), and the application populates that column with strings as part of its operation.

For strings of sizes > ~30 MB we find that once the insertion\update is complete, a part of the string gets truncated and what is stored in the server is not the full string. The thing is the strings get truncated at variable positions (we are still left with over 30 MB of data after the truncation), so there isn't some fixed point that can lead me in the direction of some size limitation (though this still may be the case).

I am not seeing any errors in insertion time, although at times I notice that during such long insertions\updates the SQL server closes the connection - But shouldn't the operation get rolled back in this case?

Would appreciate some ideas. Not sure how to proceed.

Answer 1

I would suspect it's not SQL Server or even the connection inserting the data. I wonder if it is corruption of the data when setting the command parameter value or even within the application. I've experimented with connection and command timeouts to induce them while inserting, and your assumption that it would rollback the canceled implicit transaction is correct. Here's an example:

// Use connection with timeout of 1 second to generate a timeout when inserting
using (var conn = new SqlConnection(@"Data Source=(localdb)\mssqllocaldb;Integrated Security=SSPI;Initial Catalog=tempdb;Connection Timeout=1"))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandTimeout = 1;
    conn.Open();
    cmd.CommandText = @"if not exists(select * from sys.tables where name = 'LongStringTruncation')
                        begin
                            create table LongStringTruncation (Data nvarchar(max));
                        end";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "insert LongStringTruncation values (@t)";
    var t = cmd.CreateParameter();
    t.DbType = DbType.String;
    t.ParameterName = "@t";
    t.Value = new String('A', 30000000); // 30,000,000 chars = 60MB
    cmd.Parameters.Add(t);
    cmd.ExecuteNonQuery();
}

When this succeeds (i.e. the query completes before timeout); the following query shows all 30,000,000 characters were transmitted and inserted; when it fails with a timeout, the table is empty.

select len(Data) from LongStringTruncation;

Note that I've used SQL 2014 here, so it could be there is a bug in SQL 2008 R2, which this test could uncover. My test also uses parameters, and if you are instead using string concatenation to build your SQL, this could be another source of problems.

If neither of these explain the issue, then the only conclusion that makes sense is that the application itself is truncating the data in some way before inserting it. Given the random nature, I'd consider that the way you get the string data (e.g. a buffered stream without flushing before reading the result), or some multithreaded race when getting the value / signaling that it is complete would be my first suspects.