sp_dbcc_summaryreport

Generates a summary report on the specified database.

Syntax

sp_dbcc_summaryreport [dbname [, date [, op_name [,
	display_recommendations]]]]

Parameters

dbname – specifies the name of the database for which you want the report generated. If you do not specify dbname, sp_dbcc_summaryreport generates reports on all databases in dbccdb..dbcc_operation_log for which the date is on or before the date and time specified by the date option.
date – specifies the date on which dbcc checkstorage was performed. If you do not specify a date, sp_dbcc_summaryreport uses the date of last dbcc checkstorage operation performed on the target database. This parameter is of the datatype datetime. If both the date and the time are specified for date, summary results of all the operations performed on or before the specified time are reported. If no date is specified, all operations are reported.
opname – specifies the operation. opname may be either checkstorage, which is the default, or checkverify, or both. If opname is not specified, reports are generated for all operations.
display_recommendations – enables reporting the recommendations generated by sp_dbcc_recommendations

Examples

Example 1 – Generates a summary report on the sybsystemprocs database, providing information on all dbcc checkstorage and dbcc checkverify operations performed:

sp_dbcc_summaryreport

DBCC Operation : checkstorage

Database Name      Start time           End Time    Operation ID
    Hard Faults Soft Faults Text Columns Abort Count     User Name
------------------ -------------------- ----------- ------------
    ----------- ----------- ------------ -----------    ------------------
sybsystemprocs     05/11/1999 14:53:11  14:53:32:163            1
              0           0            0           0     sa
sybsystemprocs     05/11/1999 14:55:06  14:55:29:200            2
              0           0            0           0     sa
sybsystemprocs    05/11/1999 14:56:10  14:56:27:750             3
              0           0            0           0     sa

DBCC Operation : checkverify

Database Name      Start time           End Time    Operation ID
    Hard Faults    Soft Faults     User Name
------------------ -------------------- ----------- ------------
    -------------- --------------- --------------------- 
sybsystemprocs     05/11/1999 14:55:29  14:55:29:310            2
                 0               0 sa

Example 2 – Generates a summary report on the user database testdb, providing information on all dbcc checkstorage operations performed. dbcc checkstorage was the only operation run on this database, so no dbcc checkverify information appears on the report:

sp_dbcc_summaryreport "testdb"

DBCC Operation : checkstorage

Database Name   Start time           End Time    Operation ID
    Hard Faults Soft Faults Text Columns Abort Count  User Name
--------------- -------------------- ------------ ------------
    ----------- ----------- ------------ -----------  ----------------
testdb         05/11/1999 14:55:29  14:55:49:903  1
     0           0            0           0           sa
testdb         05/11/1999 14:55:50  14:56:9:546   2
     0           0            0           0           sa
 testdb         05/11/1999 14:56:28  14:56:40:666  3
     0           0            0           0           sa

Example 3 – Generates a summary report on the sybsystemprocs database, providing information on all dbcc checkverify operations performed. Because dbcc checkverify was the specified operation, no dbcc checkstorage information appears on the report:

1> sp_dbcc_summaryreport null, null, "checkverify"
2> go

DBCC Operation : checkverify

 Database Name  Start time            End Time      Operation ID  Run Srl     Table Name   Table Id    Hard Faults   Soft Faults  User Name
 -------------- -------------------- ------------- ------------ -------
    ------------ ----------- -----------    ------------ ----------------  testdb         08/31/2004 11:06:11  11:6:11:370             3         1
    NULL          NULL                  0            0  sa

(1 row affected)

Example 4 – Generates a summary report on the sybsystemprocs database, providing information on all dbcc checkstorage operations performed. Because dbcc checkstorage was the specified operation, no dbcc checkverify information appears on the report:

sp_dbcc_summaryreport sybsystemprocs, null, "checkstorage"

DBCC Operation : checkstorage

Database Name   Start time           End Time    Operation ID
    Hard Faults Soft Faults Text Columns Abort Count  User Name
--------------- -------------------- ------------ ------------
    ----------- ----------- ------------ -----------  ---------
sybsystemprocs   05/11/1999 14:53:11  14:53:32:163  1
    0           0            0           0            sa
sybsystemprocs   05/11/1999 14:55:06  14:55:29:200  2
    0           0            0           0            sa
sybsystemprocs   05/11/1999 14:56:10  14:56:27:750  3
    0           0            0           0            sa

Example 5 – Adds recommended fixes to the summary report of database my_db:
```
sp_dbcc_summaryreport @dbname = my_db, @display_recommendations = 1
```

Usage

There are additional considerations when using sp_dbcc_summaryreport:

sp_dbcc_summaryreport generates a summary report of checkstorage or checkverify operations, or both, on the specified database.
The report indicates the name of the database that was checked, the start and end time of the dbcc checkstorage run and the number of soft and hard faults found.
The “Operation ID” column contains a number that identifies the results of each dbcc checkstorage operation on a given database at a specific time. The number provided in the report comes from the opid column of the dbcc_operation_log table. See the System Administration Guide for more information.
The “Text Columns” column shows the number of non-null text columns found by dbcc checkstorage during the run.
The “Abort Count” column shows the number of tables that contained errors, which caused dbcc checkstorage to abort the check on the table. For details on the errors, run sp_dbcc_faultreport.

See also dbcc in Reference Manual: Commands.

Permissions

The permission checks for sp_dbcc_summaryreport 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_summaryreport.