In this case it is a background task that wakes up and is attempting to deallocate and encountering the damaged ‘so called’ IAM page. I suspect it is like Paul mentioned that the pointer in the sysallocunits was some-how damaged or the IAM chain was damaged. The IAM page was re-used or the chain pointer was bad to the back-ground task keeps trying to access the IAM to deallocate and it can’t.
For this specific issue we might have been able to disable deferred deallocation but you would have to know that. (-T670)
· The error 5243 is an automatic dump trigger setup for corruption capture that you can’t control with dbcc dumptrigger(‘clear’, 5243)
· The –T2558 is not to disable all dumps it disables DBCC CHECKDB Watson integration and since you were not hitting CHECKDB errors in this code it won’t change things.
· You can generically use –T2542 (skip mini-dump)
· Trace flag –T3656 disables loading of dbghelp so the stack should not be put in the error log if this is enabled
· The dump logic also has some limitations in that if you had had full or filtered dumps enabled it will only take ## before it downgrades to mini-dump only as well. (See registry section below)
· Worse come to worse you could have renamed SQLDumper.exe for a bit until the problem was corrected. This would prevent .mdmp’s but not the error log growth.
· If error is severity 20 or higher the dump is automatically generated.
· Trace flag –T8026 tells dump trigger to remove the trigger after the first dump has been triggered. (Does not disable the automatic triggers)
· If you enable trace flag –T3628 it can include other errors based on a severity.
· If you enable trace flag –T3629 it will include messages marked to include with this trace flag enabled.
Automatic Dumping Control
A bunch of the automatic, message dumping information is controlled by the message formatting specifications. When combined with some of the trace flags I just outlined you can determine the behaviors. This information is contained in an internal array returning if the message should always log, always dump, or dump only if trace flag 3629 is enabled. The array is generated from a header file so it is not just something I can pull up and show you easily. It is actually part of the build that takes a header, parses comments and generates the array.
Here are some of the errors that are setup to dump.
There are some registry keys to set the dump limits as well. By default it is set to (0) indicating INFINITE.
Under the Setup base key location: (EX: HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL10.SQL2008Setup)
· MaxDumps (DWORD) value
· MaxFullDumps (DWORD) value
· SQLExceptionDumpPath (String) value
Display Dump Triggers
You can use dbcc dumptrigger(‘display’) to see the currently installed dump triggers on the system. We sure to enabled dbcc tracon(3604) to retrieve the output of the command.
Page (1:5882160) in database ID 8 is allocated in the SGAM (1:5623553) and PFS (1:5879976), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:5882163) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057599386320896 (type Unknown), but it was not detected in the scan.