SQLServer 错误: 8646，在数据库 'msdb'，表 1077578877 的索引 ID 1 中找不到索引条目

一台非生产服务器，磁盘被打满了，刚开始以为只是正常的磁盘使用增长满了，就清理了一部分空间出来，没过一会，就又满了，排查到sqlserver的错误日志文件一直在增

 

同时查看sqlserver错误日志，大量报错日志：

[298] SQLServer 错误: 8646，在数据库 'msdb'，表 1077578877 的索引 ID 1 中找不到索引条目。指示的索引已损坏，或当前的更新计划有问题。请运行 DBCC CHECKDB 或 DBCC CHECKTABLE。如果该问题仍然存在，请与产品支持人员联系。 [SQLSTATE HY000] (ConnExecuteCachableOp)

先是执行dbcc checktable等，没有解决

DBCC CHECKTABLE ('dbo.sysjobhistory') WITH PHYSICAL_ONLY;
GO

DBCC CHECKTABLE ('dbo.sysjobhistory',1);


DBCC CHECKTABLE ('dbo.sysjobhistory',2);


DBCC CHECKTABLE ('dbo.sysjobhistory');

有报错信息，报错信息大概100多条：
数据行(1:6197:21)由(instance_id = 210664112)标识，索引值为“job_id = '23DCC31A-1C40-4E2B-AE58-8ED4428CB2EA' and instance_id = 210664112”。
从表里查询这个条件，也能查出来数据，后来想到可能是索引的问题。
把索引删除重建，问题解决。

后来又想到，其实索引重建可能也可以解决。

-- 重建索引
ALTER INDEX IX_indexname ON tablename REBUILD WITH (ONLINE = ON)
-- 更新统计信息
UPDATE STATISTICS tablename [IX_indexname]

 

附查询索引相关信息代码：

--生成表索引的创建删除语句

;
WITH    TB
          AS ( SELECT   TB.object_id ,
                        Schema_name = Sch.name ,
                        table_name = TB.name
               FROM     sys.tables TB
                        INNER JOIN sys.schemas Sch ON TB.schema_id = Sch.schema_id
               --WHERE    TB.is_ms_shipped = 0  --系统对象
             ),
        IXC
          AS ( SELECT   IXC.object_id ,
                        IXC.index_id ,
                        IXC.index_column_id ,
                        IXC.is_descending_key ,
                        IXC.is_included_column ,
                        column_name = C.name
               FROM     SYS.index_columns IXC
                        INNER JOIN SYS.columns C ON IXC.object_id = C.object_id
                                                    AND IXC.column_id = C.column_id
             ),
        IX
          AS ( SELECT   IX.object_id ,
                        index_name = IX.name ,
                        index_type_desc = IX.type_desc ,
                        IX.is_unique ,
                        IX.is_primary_key ,
                        IX.is_unique_constraint ,
                        IX.is_disabled ,
                        index_columns_TEMP = STUFF(IXC_COL.index_columns, 1, 1,
                                                   N'') ,
                        index_columns = CASE WHEN IXC_COL_INCLUDE.index_columns_includes IS NOT NULL
                                             THEN STUFF(LEFT(IXC_COL.index_columns,
                                                             DATALENGTH(IXC_COL.index_columns)
                                                             - DATALENGTH(IXC_COL_INCLUDE.index_columns_includes)),
                                                        1, 1, N'')
                                             ELSE STUFF(IXC_COL.index_columns,
                                                        1, 1, N'')
                                        END ,
                        index_columns_includes = STUFF(IXC_COL_INCLUDE.index_columns_includes,
                                                       1, 1, N'')
               FROM     sys.indexes IX
                        CROSS APPLY ( SELECT    index_columns = ( SELECT
                                                              N','
                                                              + QUOTENAME(column_name)
                                                              FROM
                                                              IXC
                                                              WHERE
                                                              object_id = IX.object_id
                                                              AND index_id = IX.index_id
                                                              ORDER BY index_column_id
                                      FOR       XML PATH('') ,
                                                    ROOT('r') ,
                                                    TYPE      
   ).value('/r[1]', 'nvarchar(max)')
                                    ) IXC_COL
                        OUTER APPLY ( SELECT    index_columns_includes = ( SELECT
                                                              N','
                                                              + QUOTENAME(column_name)
                                                              FROM
                                                              IXC
                                                              WHERE
                                                              object_id = IX.object_id
                                                              AND index_id = IX.index_id
                                                              AND is_included_column = 1
                                                              ORDER BY index_column_id
                                      FOR       XML PATH('') ,
                                                    ROOT('r') ,
                                                    TYPE      
   ).value('/r[1]', 'nvarchar(max)')
                                    ) IXC_COL_INCLUDE
               WHERE    index_id > 0
             )
    SELECT  DB_NAME() AS N'数据库名' ,
            TB.Schema_name AS N'架构' ,
            TB.table_name AS N'表名' ,
            IX.index_name AS N'索引名' ,
            IX.index_type_desc AS N'索引类型' ,
            IX.is_unique AS N'是否唯一索引' ,
            IX.is_primary_key AS N'是否主键' ,
            IX.is_unique_constraint AS N'是否唯一约束' ,
            IX.is_disabled AS N'是否禁用索引' ,
            IX.index_columns AS N'索引列' ,
            IX.index_columns_includes AS N'索引包含列' ,
            N'CREATE INDEX ' + N'[' + IX.index_name + N']' + N' ON ' + N''
            + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
            + QUOTENAME(TB.table_name) + N'(' + IX.index_columns + N')'
            + CASE WHEN IX.index_columns_includes IS NOT NULL
                   THEN CHAR(13) + N'INCLUDE (' + IX.index_columns_includes
                        + N')'
                   ELSE N''
              END AS N'创建索引' ,
            N'DROP INDEX ' + QUOTENAME(IX.index_name) + N' ON  '
            + QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(TB.SCHEMA_NAME) + N'.'
            + QUOTENAME(TB.table_name) AS N'删除索引'
    FROM    TB
            INNER JOIN IX ON TB.object_id = IX.object_id
    ORDER BY Schema_name ,
             table_name ,
             IX.index_name

 

收尾工作：
1.清除错误日志
    

exec sp_cycle_agent_errorlog

exec sp_cycle_errorlog

 

2.删除错误   D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log     目录下错误日志文件。