SQLServer不断产生mdmp文件

 

今天遇到一个灵异事件：安装了SQLServer的一台机器C盘空间满了，发现在C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG目录下有很多mdmp/log/txt文件

 

打开一个log文件提示：

Unable to find index entry in index ID 1, of table 117575457, in database 'msdb'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

 

看来是msdb中某个表的索引坏了，执行：

DBCC CHECKDB('msdb')

 

发现一个错误：

Table error: table 'sysjobhistory' (ID 117575457). Index row in index 'nc1' (ID 2) does not match any data row.

 

执行脚本修复该错误：

DECLARE tempcursor CURSOR READ_ONLY FOR select spid From master..sysprocesses where dbid = (select database_id from sys.databases where name = 'msdb') DECLARE @name int OPEN tempcursor FETCH NEXT FROM tempcursor INTO @name WHILE (@@fetch_status <> -1) BEGIN exec('kill ' + @name) FETCH NEXT FROM tempcursor INTO @name END Use msdb Go sp_dboption 'msdb', 'single_user', 'true' GO DBCC CHECKDB('msdb', REPAIR_REBUILD) go sp_dboption 'msdb', 'single_user', 'false' CLOSE tempcursor DEALLOCATE tempcursor