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.
