一台非生产服务器，磁盘被打满了，刚开始以为只是正常的磁盘使用增长满了，就清理了一部分空间出来，没过一会，就又满了，排查到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 目录下错误日志文件。
标签：IX,name,index,IXC,SQLServer,msdb,索引,id,columns From： https://www.cnblogs.com/davidhou/p/18359218