sp_dbcc_faultreport

Description

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.

Syntax

sp_dbcc_faultreport [report_type [, dbname [, objectname
	[, date [, hard_only [, exclusion_mode[, exclusion_faults
	[, exclusion_tables [, exclusion_combo 
	[, display_recommendations [, opid [,fault_type_in]]]]]]]]]]]]

Parameters

Examples

Example 1

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

Example 2

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)
.
.
.

Example 3

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 generates 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 generates faults up to 9:59.

Example 4

Generates a short form report only for hard faults reported by the latest checkstorage run for a database called mydb:

sp_dbcc_faultreport short, mydb, @hard_only = 1

Example 5

Adds recommended fixes to the fault report of database my_db:

sp_dbcc_faultreport @dbname = my_db, 
    @display_recommendations = 1

Example 6

Generates a fault report that does not contain fix recommendations:

sp_dbcc_faultreport @dbname = my_db

Example 7

Runs sp_dbcc_faultreport on database my_db with the persistent exclusion list disabled:

sp_dbcc_faultreport @dbname = 'my_db', @exclusion_mode = 'ignore'

Example 8

Runs sp_dbcc_faultreport on database my_db with the persistent exclusion list enabled and extended to exclude from processing fault type 100036:

sp_dbcc_faultreport @dbname = 'my_db', @exclusion_mode = 'extend', 
    @exclusion_faults = '100036'

Example 9

Runs sp_dbcc_faultreport on database my_db with the persistent exclusion list enabled and extended to exclude from processing and the table tab:

sp_dbcc_faultreport @dbname = 'my_db', @exclusion_mode = 'extend',
    @exclusion_tables = 'tab'

Example 10

Runs sp_dbcc_faultreport on database my_db with the persistent exclusion list disabled and an enabled temporary exclusion list that excludes from processing the table tab and fault type 100036:

sp_dbcc_faultreport @dbname = 'my_db', @exclusion_mode = 'ignore', 
    @exclusion_faults = '100036', @exclusion_tables = 'tab'

Example 11

Runs sp_dbcc_faultreport on database my_db with the persistent exclusion list disabled and an enabled temporary exclusion list that excludes from processing fault type ‘100002’ pertaining to the table mytable and fault type 100035 pertaining to the table tab:

sp_dbcc_faultreport @dbname = 'my_db', @exclusion_mode = 'ignore', 
    @exclusion_combo ='mytable:100002, tab:100035'

Example 12

Generates a long form report for the 100029 faults reported by the latest checkstorage run for the mydb database (100029 is the fault type for page header errors):

sp_dbcc_faultreport long, mydb, @fault_type_in = 100029

Example 13

Generates a short form report for faults reported by the checkstorage run with operation ID 5 for the mydb database:

sp_dbcc_faultreport short, mydb, @opid = 5

Usage

• 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.

Permissions

The permission checks for sp_dbcc_faultreport differ based on your granular permissions settings.

See also

See the type_code column described in the System Administration Guide for information on the fault ID and on the fault status.