I have just encounted an unusual database problem. My dev BizTalk server suddenly ran out of space. Initially I thought it was the transaction logs. No luck there they were well within normal limits. I did check the size ot the
'Microsoft SQL Server' directory. It was over 8GB. The volume was traced to the LOG directory. SQL was pumping out 4 MB of error log every 10 seconds. After fighting with the server to free up some disk space, I checked out the error files. The parts appear to be:
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump7243.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 06/17/09 15:52:15 spid 54
*
* CPerIndexMetaQS::ErrorAbort - Index corruption
*
* Input Buffer 510 bytes -
* EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xBE810
* 129F3D1574AA4EB72EE897A6CCC, @step_id = 0, @sql_message_id = 0, @sql_sev
* erity = 0, @run_status = 1, @run_date = 20090617, @run_time = 155201, @r
* un_duration = 3, @operator_id_emailed = 0, @operator_id_netsent = 0, @op
* erator_id_paged = 0, @retries_attempted = 0, @session_id = 65, @message
* = N'The job succeeded. The Job was invoked by Schedule 4 (Schedule). T
* he last step to run was step 1 (Purge).'
And
2009-06-17 15:52:14.55 spid56 Error: 8646, Severity: 21, State: 1.
2009-06-17 15:52:14.55 spid56 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.
2009-06-17 15:52:15.24 spid54 Using 'dbghelp.dll' version '4.0.5'
2009-06-17 15:52:15.24 spid54 **Dump thread - spid = 54, PSS = 0x0DDD32D0, EC = 0x0DDD32D8
2009-06-17 15:52:15.24 spid54 ***Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\SQLDump7243.txt
2009-06-17 15:52:15.24 spid54 * *******************************************************************************
2009-06-17 15:52:15.24 spid54 *
2009-06-17 15:52:15.24 spid54 * BEGIN STACK DUMP:
2009-06-17 15:52:15.24 spid54 * 06/17/09 15:52:15 spid 54
2009-06-17 15:52:15.24 spid54 * 2009-06-17 15:52:15.24 spid54 * CPerIndexMetaQS::ErrorAbort - Index corruption
Searching for 'CPerIndexMetaQS::ErrorAbort - Index corruption' online led me to a post that suggested running the following SQL script from SQL Server Manager on the local machine.
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
The script found and repaired the corrupt index, as shown below.
DBCC results for 'sysdownloadlist'.
There are 0 rows in 0 pages for object "sysdownloadlist".
DBCC results for 'sysjobhistory'.
Repair: Successfully deleted row in index "dbo.sysjobhistory, nc1" in database "msdb".
Msg 8952, Level 16, State 1, Line 1
Table error: table 'sysjobhistory' (ID 117575457). Index row in index 'nc1' (ID 2) does not match any data row. Possible extra or invalid keys for:
The error has been repaired.
Msg 8956, Level 16, State 1, Line 1
Index row (1:1141:114) with values (job_id = '89F8A96E-AF2B-4BE9-B5E4-FEF3B8C94EC5' and instance_id = 466459) pointing to the data row identified by (instance_id = 466459).
There are 6640 rows in 459 pages for object "sysjobhistory".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysjobhistory' (object ID 117575457).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'sysjobhistory' (object ID 117575457).
The log file generation has stopped and the server appears to be back to normal.