sp_dbcc_faultreport

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

report_type – specifies the type of fault report. Valid values are short and long. The default is short.
dbname – 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.
object_name – 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.
date – 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, the SAP ASE 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.

Note: 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.
hard_only – enables the reporting of hard faults when you specify 1. Valid values are 0 or 1, and the default is 0.
display_recommendations – enables reporting the recommendations generated by sp_dbcc_recommendations, and the parameters exclusion_mode, exclusion_faults, exclusion_tables, display_recommendations, and exclusion_combo refer to exclusion support and are optional.
exclusion_mode – is a varchar and is on by default. To disable this, you must provide an “ignore” each time the sp_dbcc_faultreport is run. Use either of the following:
- ignore – ignores the persistent exclusion list and uses the temporary exclusion list, if one is provided (type, varchar).
- extend – applies the temporary exclusion list as well as the persistent exclusion list (type, varchar).
exclusion_faults – is a comma-separated list of fault types to be excluded from reporting (type, varchar).
exclusion_tables – is a comma-separated list of tables to be excluded from reporting (type is varchar).
exclusion_combo – is a comma-separated list of fault/table combinations to be excluded from reporting (type is varchar).
opid – enables fault reporting for a specific—instead of latest—operation ID for a specific date. No operation ID is specified by default.
fault_type_in – enables fault reporting for a specific fault type. The default is NULL.

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

There are additional considerations when using sp_dbcc_faultreport:

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.

Setting	Description
Enabled	With granular permissions enabled, you must be the database owner of `dbccdb` (or `dbccalt`), or have the `report` `checkstorage` privilege on the specified database.
Disabled	With granular permissions disabled, any valid user for the database name specified can run sp_dbcc_faultreport.