sp_dbcc_exclusions  sp_dbcc_fullreport

Chapter 4: dbcc Stored Procedures

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

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

NoteTo 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:

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

Permissions

Any valid user for the database name specified can run sp_dbcc_faultreport.

See also

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





Copyright © 2005. Sybase Inc. All rights reserved. sp_dbcc_fullreport

View this book as PDF