断电后存储过程报Cannot create a row of size错误的原因与解决方案

断电后，一款用于数据库间数据转换插入的存储过程开始报错，实际数据远未达到报错提及的大小，且断电前运行正常。目前仅能通过重建目标表并迁移旧数据解决，但问题已多次出现，现咨询：该问题产生的原因是什么？是否有更优解决方案？

错误信息 #

Msg 511, Level 16, State 1, Line 3
Cannot create a row of size 8067 which is greater than the allowable maximum row size of 8060.

已尝试但无效的操作 #

DBCC CLEANTABLE 
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS
DBCC CHECKDB
rebuilding the index 

目标表定义 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Requests](
    [Autokey] [bigint] IDENTITY(1,1) NOT NULL,
    [RequesterName] [nvarchar](50) NULL,
    [CustomerId] [nvarchar](12) NULL,
    [DateReq] [nvarchar](8) NULL,
    [TimeReq] [nvarchar](4) NULL,
    [Registrar] [nvarchar](30) NULL,
    [Description] [nvarchar](100) NULL,
    [IsActive] [bit] NULL,
    [Subject] [nvarchar](250) NULL,
    [ChkApproveRR] [bit] NULL,
    [RemRR] [nvarchar](100) NULL,
    [Remwork] [nvarchar](100) NULL,
    [ContractId] [nvarchar](12) NULL,
    [FlagArchive] [char](1) NULL,
    [InvoiceNo] [nvarchar](20) NULL,
    [SerialAcc] [nvarchar](20) NULL,
    [FiscalPeriod] [smallint] NULL,
    [DateFinish] [nvarchar](8) NULL,
    [CustRequestNo] [nvarchar](20) NULL,
    [IsAdvance] [bit] NULL,
    [DlvDeviceKey] [bigint] NULL,
    [WebRequestNo] [bigint] NULL,
    [WebRequestAutoKey] [bigint] NULL,
    [Uploaded] [bit] NULL,
    [ChkApproveWork] [bit] NULL,
    [ScanPath] [nvarchar](100) NULL,
    [computer] [bit] NULL,
    [Tracker] [nvarchar](12) NULL,
 CONSTRAINT [PK_Requests] PRIMARY KEY NONCLUSTERED 
(
    [Autokey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Requests] ADD  CONSTRAINT [DF__RequestUpload]  DEFAULT ((0)) FOR [Uploaded]
GO
ALTER TABLE [dbo].[Requests] ADD  CONSTRAINT [DF__RequestsChkApp]  DEFAULT ((0)) FOR [ChkApproveWork]
GO

存储过程代码 #

CREATE PROCEDURE [dbo].[SP_AddRequestUsingCustomerServiceRequestInAutomation] @JobId nvarchar(50), @JobNo bigint
AS 

DECLARE @StrSql nvarchar(4000)

BEGIN
        
        If Not Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'ServiceRequestNoTemp' 
        And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
            EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] ADD [ServiceRequestNoTemp] nvarchar(50)') AT [IPAddress]

        If Not Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'CustomerAccDetailTemp' 
        And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
            EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] ADD CustomerAccDetailTemp nvarchar(50)')  AT [IPAddress]

        If Not Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'ItemDescriptionTemp' 
        And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
            EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] ADD ItemDescriptionTemp nvarchar(255)')  AT [IPAddress]
            
        If Not Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'SerialTemp' 
        And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
            EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] ADD SerialTemp nvarchar(200)')  AT [IPAddress]

        SET @StrSql='INSERT INTO [IPAddress].[DBMK].[dbo].[Requests](WebRequestNo,RequesterName,Description,Registrar,ServiceRequestNoTemp,CustomerAccDetailTemp,ItemDescriptionTemp,SerialTemp)
        SELECT '''+ CAST(@JobNo AS varchar(50)) +''',LEFT(s.Applicant,50),LEFT(s.Description,100),CASE WHEN ISNULL(rUser.LastName,'''')>'''' THEN LEFT(rUser.LastName,30) ELSE LEFT(lUser.LegalName,30) END AS Registar
        ,  CAST(j.JobNo AS varchar(50)) ,p.AccDetailsId ,LEFT(s.ItemDescription,255),s.Serial
        FROM [DBSource].dbo.CustomerServiceRequests s
        LEFT JOIN [DBSource].dbo.Jobs j ON s.Id = j.SourceId
        LEFT JOIN [DBSource].dbo.Positions pos ON pos.Id=s.RequesterPositionId
        LEFT JOIN [DBSource].dbo.People p ON pos.PersonId =p.Id
        LEFT JOIN [DBSource].dbo.PersonReals r ON p.PersonRealId=r.Id 
        LEFT JOIN [DBSource].dbo.Positions posUser ON posUser.Id=s.RequesterPositionId
        LEFT JOIN [DBSource].dbo.People pUser ON posUser.PersonId =pUser.Id
        LEFT JOIN [DBSource].dbo.PersonReals rUser ON pUser.PersonRealId=rUser.Id  
        LEFT JOIN [DBSource].dbo.PersonLegals lUser ON pUser.PersonLegalId =lUser.Id 
        WHERE j.Id=''' + @JobId + ''''
        print (@StrSql)
        EXEC (@StrSql) 

        SET @StrSql='Update [DBMK].[dbo].[Requests]
        SET DateReq= [DBMK].[dbo].[NumericFullDate] (GETDATE()) , TimeReq=REPLACE(LEFT((convert(time(0), GETDATE ())),5) ,'':'','''')
        ,CustomerId=(SELECT ISNULL(c.CustId,'''') FROM [DBMK].[dbo].[AccDetails] a
        LEFT JOIN [DBMK].[dbo].[Customers] c ON a.DetailCode  =c.DetailCode  
        LEFT JOIN [DBMK].[dbo].[Requests] r ON r.CustomerAccDetailTemp=a.GUID
        WHERE r.ServiceRequestNoTemp='''+ CAST(@JobNo AS varchar(50)) +''')
        WHERE ServiceRequestNoTemp='''+ CAST(@JobNo AS varchar(50)) +'''
        print (@StrSql)
        EXEC (@StrSql) AT [IPAddress]

        SET @StrSql='INSERT INTO [IPAddress].[DBMK].[dbo].[Activitys](RequestedNumber ,ActivityCode ,CustomerId , Description,SoftwareID )
        SELECT r.Autokey,''10'',r.CustomerId,rs.Description +'' ''+IsNull(rs.ExtraDescription,'''')+'' ''+''Path ''+IsNull(rs.InstallationLocation,'''')+'' ''+ CASE WHEN ISNULL(r.SerialTemp,'''')>'''' THEN '' ('' + r.SerialTemp +'')'' ELSE '''' END ,s.SysId 
        FROM [IPAddress].[DBMK].[dbo].[Requests] r
        LEFT JOIN [IPAddress].[DBMK].[dbo].[Systems] s ON r.ItemDescriptionTemp=s.description 
        LEFT JOIN [DBSource].dbo.Jobs j ON j.JobNo=r.ServiceRequestNoTemp
        LEFT JOIN [DBSource].dbo.CustomerServiceRequests rs ON rs.Id = j.SourceId
        WHERE r.ServiceRequestNoTemp='''+ CAST(@JobNo AS varchar(50)) +'''
        print (@StrSql)
        EXEC (@StrSql)-- AT [IPAddress]
END

If Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'ServiceRequestNoTemp' 
And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
    EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] DROP COLUMN ServiceRequestNoTemp')AT [IPAddress]     

If Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'CustomerAccDetailTemp' 
And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
    EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] DROP COLUMN CustomerAccDetailTemp') AT [IPAddress]           

If Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'ItemDescriptionTemp' 
And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
    EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] DROP COLUMN ItemDescriptionTemp')     AT [IPAddress]

If Exists(Select * From [IPAddress].[DBMK].dbo.sysColumns Where name = 'SerialTemp' 
And id = (Select t.object_id From [IPAddress].[DBMK].sys.tables As t Where t.[name] = PARSENAME('[IPAddress].[DBMK].[dbo].[Requests]', 1)))
    EXEC ('ALTER TABLE [DBMK].[dbo].[Requests] DROP COLUMN SerialTemp') AT [IPAddress]  
GO

注：错误发生在第一个INSERT语句处，且仅在断电后触发。

问题原因分析 #

1. 表元数据损坏：断电导致SQL Server未完成临时列删除的事务提交，存储过程反复添加/删除临时列的操作，可能留下无效元数据记录，导致SQL Server计算行大小时错误累加。
2. 堆表空间管理异常：目标表Requests是非聚集主键（即堆结构），堆表的页分配和维护逻辑不如聚集索引稳定，断电易引发页内空间管理混乱，导致行大小误判。
3. 临时列元数据残留：临时列删除操作未彻底清理元数据，这些残留的列定义被计入行总大小，最终超过8060字节的限制。

更优解决方案 #

临时应急方案（无需重建表） #

1. 彻底清理表残留数据：执行带FULL选项的DBCC CLEANTABLE，清理列删除后留下的无效空间和元数据：

   DBCC CLEANTABLE ('你的数据库名', 'dbo.Requests', FULL)
   

2. 重建堆表结构：对堆表执行重建操作，修复页内空间管理异常：

   ALTER TABLE dbo.Requests REBUILD
   

长期预防方案 #

1. 替换动态列操作：用临时表替代动态修改目标表结构的逻辑，创建临时表存储中间数据，避免频繁修改正式表结构：

   CREATE TABLE #TempRequests (
       WebRequestNo bigint,
       RequesterName nvarchar(50),
       Description nvarchar(100),
       Registrar nvarchar(30),
       ServiceRequestNoTemp nvarchar(50),
       CustomerAccDetailTemp nvarchar(50),
       ItemDescriptionTemp nvarchar(255),
       SerialTemp nvarchar(200)
   )
   -- 插入数据到临时表，再关联目标表完成后续操作
   

2. 将堆表改为聚集索引表：把主键改为聚集索引，提升表结构稳定性：

   ALTER TABLE dbo.Requests DROP CONSTRAINT PK_Requests
   GO
   ALTER TABLE dbo.Requests ADD CONSTRAINT PK_Requests PRIMARY KEY CLUSTERED (Autokey ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
   GO
   

3. 增强数据库容错：配置Always On故障转移集群，或定期执行完整备份+日志备份，降低断电导致的事务中断和数据损坏风险。

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