sp_plan_dbccdb

Recommends suitable sizes for new dbccdb and dbccalt databases, lists suitable devices for dbccdb and dbccalt, and suggests a cache size and a suitable number of worker processes for the target database.

Syntax

sp_plan_dbccdb [dbname]

Parameters

dbname – specifies the name of the target database. If dbname is not specified, sp_plan_dbccdb makes recommendations for all databases in master..sysdatabases.

Examples

Example 1 – Returns configuration recommendations for creating a dbccdb database suitable for checking the master database. The dbccdb database already existed at the time this command was run, so the size of the existing database is provided for comparison:

sp_plan_dbccdb master

Recommended size for dbccdb database is 50MB (data = 48MB, log = 2MB).
dbccdb database already exists with size 280MB.
Recommended values for workspace size, cache size and process count are:
dbname                   scan ws  text ws  cache  comp mem  process count

master                   128K     48K      640K   0K        1

Example 2 – Returns configuration recommendations for creating a dbccdb database suitable for checking all databases in the server. The output includes Compresssion Memory Requirement, which has a non-zero value only for archive databases using any compressed device. No dbccdb database existed at the time this command was run:

sp_plan_dbccdb

Recommended size for dbccdb database is 50MB (data = 48MB, log = 2MB).
dbccdb database already exists with size 280MB.
Recommended values for workspace size, cache size and process count are:
dbname                   scan ws  text ws  cache  comp mem  process count

master                   128K     48K      640K   0K        1
tempdb                   656K     176K     1280K  0K        2
model                    64K      48K      640K   0K        1
sybsystemdb              64K      48K      640K   0K        1
sybsystemprocs           1488K    384K     640K   0K        1
sybsecurity              272K     80K      1280K  0K        2
adb                      80K      64K      1920K  12M       3

Example 3 – Returns configuration recommendations for creating a dbccdb database suitable for checking pubs2:

sp_plan_dbccdb pubs2

Recommended size for dbccdb is 4MB.
Recommended devices for dbccdb are:
Logical Device Name      Device Size Physical Device Name
sprocdev                 28672      /remote/sybase/devices/srv_sprocs_dat
tun_dat                  8192       /remote/sybase/devices/srv_tun_dat
tun_log                  4096       /remote/sybase/devices/srv_tun_log
Recommended values for workspace size, cache size and process count are:
dbname     scan ws    text ws    cache    process count
pubs2      64K        64K        640K     1

Usage

There are additional considerations when using sp_plan_dbccdb:

sp_plan_dbccdb recommends suitable sizes for creating new dbccdb and dbccalt databases, lists suitable devices for the new database, and suggests cache size and a suitable number of worker processes for the target database.
If you specify dbccdb, sp_plan_dbccdb recommends values for dbccalt, the alternate database. If you specify dbccalt, sp_plan_dbccdb recommends values for dbccdb.
sp_plan_dbccdb does not report values for existing dbccdb and dbccalt databases. To gather configuration parameters for an existing dbccdb or dbccalt database, use sp_dbcc_evaluatedb.

See also dbcc in Reference Manual: Commands.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, any user may execute the procedure.
Disabled	With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect