Geeks With Blogs

Pseudo Knowledge Base Useful stuff I've collected... Enjoy.

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.

Posted on Wednesday, June 17, 2009 6:24 PM BizTalk , Infrastructure | Back to top


Comments on this post: BizTalk DB Log File Explosion Due to Corrupt Index

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Geordie | Powered by: GeeksWithBlogs.net