SQL Server更新100MB文件时事务日志已满问题及解决咨询

核心问题分析 #

你当前的代码通过多次UPDATE追加XML片段，每次更新都会记录整行的旧值与新值，100MB文件需要执行约1500次更新，日志会被快速撑爆；且所有操作在同一个隐式事务中，日志无法及时截断，最终触发“Transaction Log for database is full”错误。

解决方案：改用批量插入片段再合并 #

将每个XML片段作为独立行插入临时表，最后通过聚合函数合并为完整XML，大幅减少日志生成量。

步骤1：修改临时表结构 #

给TempXML表新增Sequence列，确保片段合并时顺序正确：

ALTER TABLE [DMSDataLake].[dbo].[TempXML] ADD Sequence INT NOT NULL;

步骤2：修改代码逻辑 #

1. 插入第一个片段（带序号）

private static async Task<long> InsertFirstBlock(SqlConnection connection, string text, int? xmlId)
{
    using SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@Id", xmlId ?? DBNull.Value);
    cmd.Parameters.AddWithValue("@XmlData", text);
    cmd.Parameters.AddWithValue("@Sequence", 1);
    cmd.CommandText = @"INSERT INTO [DMSDataLake].[dbo].[TempXML] (XmlFileID, XmlData, Sequence) 
                        OUTPUT INSERTED.XmlFileID 
                        VALUES (@Id, @XmlData, @Sequence); ";
    object result = await cmd.ExecuteScalarAsync();
    return Convert.ToInt64(result);
}

2. 追加片段改为插入新行

private async Task AppendBlock(SqlConnection connection, long id, string text, int sequence)
{
    using SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@XmlData", text);
    cmd.Parameters.AddWithValue("@XmlFileID", id);
    cmd.Parameters.AddWithValue("@Sequence", sequence);
    cmd.CommandText = @"INSERT INTO [DMSDataLake].[dbo].[TempXML] (XmlFileID, XmlData, Sequence) 
                        VALUES (@XmlFileID, @XmlData, @Sequence);";
    await cmd.ExecuteNonQueryAsync();
}

3. 主方法循环插入片段

public async Task<bool> AddXmlToRawXml(string filePathOnRds, string xmlContent, int sourceId, int? fileLogId, int batchSize)
{
    try
    {
        string connectionString = "my_connection_secret";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();
            int sequence = 1;
            long id = await InsertFirstBlock(connection, xmlContent.Substring(0, batchSize), fileLogId);
            
            for (int i = batchSize; i < xmlContent.Length; i += batchSize)
            {
                sequence++;
                string nextBlock = i + batchSize >= xmlContent.Length 
                    ? xmlContent.Substring(i) 
                    : xmlContent.Substring(i, batchSize);
                await AppendBlock(connection, id, nextBlock, sequence);
            }
            
            long newId = await CopyRecordFromTemp(connection, filePathOnRds, sourceId, fileLogId);
            await CleanupTempTable(connection, id); // 清理临时数据
            await connection.CloseAsync();
            return true;
        }
    }
    catch (Exception ex)
    {
        Log.Error(ex.Message);
        return false;
    }
}

4. 合并片段并插入正式表

private static async Task<long> CopyRecordFromTemp(SqlConnection connection, string path, int sourceId, int? id)
{
    using SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@XmlFileID", id);
    cmd.Parameters.AddWithValue("@FilePath", path);
    cmd.Parameters.AddWithValue("@SourceCategoryId", sourceId);
    cmd.CommandText = @"DECLARE @NewId TABLE (Id BIGINT);
                        DECLARE @FullXml NVARCHAR(MAX);

                        -- 按序号合并所有片段
                        SELECT @FullXml = STRING_AGG(XmlData, '') WITHIN GROUP (ORDER BY Sequence)
                        FROM [DMSDataLake].[dbo].[TempXML]
                        WHERE XmlFileID = @XmlFileID;

                        INSERT INTO [DMSDataLake].[dbo].[RawXML] 
                        (FilePath, XmlData, LoadedDateTime, SourceCategoryId, Status, ErrorMessage)
                        OUTPUT INSERTED.Id INTO @NewId
                        VALUES (@FilePath, CONVERT(xml, @FullXml), GETDATE(), @SourceCategoryId, 'Processed', NULL);

                        SELECT Id FROM @NewId;";
    object result = await cmd.ExecuteScalarAsync();
    return Convert.ToInt64(result);
}

5. 清理临时表（可选）

避免临时表数据堆积：

private async Task CleanupTempTable(SqlConnection connection, long xmlFileId)
{
    using SqlCommand cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@XmlFileID", xmlFileId);
    cmd.CommandText = @"DELETE FROM [DMSDataLake].[dbo].[TempXML] WHERE XmlFileID = @XmlFileID;";
    await cmd.ExecuteNonQueryAsync();
}

额外优化建议 #

• 减少日志生成：若数据库为完整恢复模式，操作前可做一次日志备份；或临时切换到简单恢复模式（操作后切回），需注意权限与数据备份。
• 表值参数优化：将所有片段打包为表值参数一次性插入，减少数据库往返次数，进一步降低日志占用。
• 批量提交事务：若片段数量极多，可每插入100个片段手动提交一次事务，避免单个事务日志过大。

内容的提问来源于stack exchange，提问作者Farley