Generates a report covering fault statistics for the dbcc checkstorage operations performed for the specified object in the target database on the specified date.The report lists the tables and indexes in order.
sp_dbcc_faultreport [report_type [, dbname [, objectname [, date ], @hard_only]]]]
specifies the type of fault report. Valid values are short and long. The default is short.
specifies the name of the target database; for example, master..sysdatabases. If dbname is not specified, the report contains information on all databases in dbccdb..dbcc_operation_log.
specifies the name of the table or index for which you want the report generated. If object_name is not specified, statistics on all objects in the target database are reported.
specifies exact date and time that the dbcc checkstorage operation finished. You can find this value in dbcc_operation_log.finish. You can create the value by combining the date from start time and the hours and minutes from end time in the sp_dbcc_summaryreport output. If you do not specify date, Adaptive Server uses the date of the most recent operation.
When you specify the date parameter, be certain that the time you enter is later than the date of the operation. sp_dbcc_faultreport cannot report faults that occur later than the time you enter in this parameter.
To focus on the date parameter, use “null” for all other parameters. If you omit a parameter entirely, sp_dbc_faultreport cannot generate a correct report.
Generates a short report of the faults found in tables in the sybsystemprocs database. The report includes the table name, the index number in which the fault occurred, the type code of the fault, a brief description of the fault, and the page number on which the fault occurred:
sp_dbcc_faultreport "short"
Database Name : sybsystemprocs Table Name Index Type Code Description Page Number -------------- ------ --------- ------------------- ----------- sysprocedures 0 100031 page not allocated 5702 sysprocedures 1 100031 page not allocated 14151 syslogs 0 100022 chain start error 24315 syslogs 0 100031 page not allocated 24315
Generates a long report of the faults found in tables in the sybsystemprocs database. This example shows the first part of the output of a long report. The complete report repeats the information for each object in the target database in which dbcc checkstorage found a fault. The data following the long string of numbers shown under the "page header" field (“Header for 14151, next 14216, previous 14150 ...”) describes the components of the "page header" string:
sp_dbcc_faultreport "long"
Generating 'Fault Report' for object sysprocedures in database sybsystemprocs. Type Code: 100031; Soft fault, possibly spurious Page reached by the chain is not allocated. page id: 14151 page header: 0x00003747000037880000374600000005000648B803EF0001000103FE0080000F Header for 14151, next 14216, previous 14150, id = 5:1 time stamp = 0x0001000648B8, next row = 1007, level = 0 free offset = 1022, minlen = 15, status = 128(0x0080) . . .
Generates a short report of faults from all tables on all databases, for an operation finished at a date and time found as an End Time, from the output of sp_dbcc_summaryreport. It is important that you use accurate end times in the date parameter; for instance, if you enter:
7/25/2000 9:58
instead of
7/25/2000 9:58:0:190
the report will generate faults only up to 9:58, not after it. You could use 9:59 if you do not want to enter the exact time the operation ends:
sp_dbcc_faultreport "short", NULL, NULL, "07/25/00 9:59"
In this case the report will generate faults up to 9:59.
sp_dbcc_faultreport generates a report that shows all faults for the specified object in the target database.
sp_dbcc_faultreport issues numerous error message number 10028 If you use:
sp_placeobject to make an object that has existing allocations put new allocations on a new segment.
sp_dropsegment to remove a segment from a fragment that contains allocations of an object assigned to that segment.
Error message number 100028 is an informational message rather than an indication of a serious error. If you prefer not to receive such messages, you can create your own reporting procedure that does not report this (or any other) error. One way to do this is to add the following to the very beginning of the standard sp_dbcc_faultreport stored procedure in the installdbccdb script:
print "removing 100028 errors from dbcc_faults table" delete dbcc_faults where type_code = 100028
If sp_dbcc_faultreport returns a number for object_name, it means the object was dropped after the dbcc checkstorage operation completed.
Any valid user for the database name specified can run sp_dbcc_faultreport.
See the type_code column described in the System Administration Guide for information on the fault ID and on the fault status.
Commands dbcc
dbcc stored procedures sp_dbcc_fullreport, sp_dbcc_statisticsreport, sp_dbcc_summaryreport, sp_dbcc_updateconfig