Planning Resources

Selecting the appropriate device and size for dbccdb is critical to the performance of dbcc checkstorage operations.

sp_plan_dbccdb provides configuration recommendations for the specified target database depending on whether dbccdb exists or not. Use this information to configure SAP ASE and set up the dbccdb database.

If dbccdb does not exist, sp_plan_dbccdb returns:

Minimum size for dbccdb
Devices that are suitable for dbccdb
Minimum sizes for the scan and text workspaces
Minimum cache size
Number of worker processes

The values recommended for the cache size are approximate, because the optimum cache size for dbccdb depends on the pattern of the page allocation in the target database. The following example from a server that uses 2K logical pages shows the output of sp_plan_dbccdb for the pubs2 database when dbccdb does not exist:

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/SERV/sprocs_dat
tun_dat               8192          /remote/SERV/tun_dat
tun_log               4096          /remote/SERV/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

(return status = 0)

If dbccdb already exists, sp_plan_dbccdb returns:

Minimum size for dbccdb
Size of existing dbccdb database
Minimum sizes for the scan and text workspaces
Minimum cache size
Number of worker processes

This example shows the output of sp_plan_dbccdb for the pubs2 database when dbccdb already exists:

sp_plan_dbccdb pubs2

Recommended size for dbccdb database is 23MB (data = 21MB, log = 2MB).
dbccdb database already exists with size 8MB.
Recommended values for workspace size, cache size and process count are:
dbname                            scan ws    text ws    cache    process count 
pubs2                             64K        48K        640K     1
(return status = 0)

If you plan to check more than one database, use the name of the largest one for the target database. If you do not provide a target database name, sp_plan_dbccdb returns configuration values for all databases listed in master..sysdatabases:

sp_plan_dbccdb

Recommended size for dbccdb is 4MB.

dbccdb database already exists with size 8MB.

Recommended values for workspace size, cache size and process count are:

dbname           scan ws    text ws    cache    process count

master           64K        64K        640K     1 
tempdb           64K        64K        640K     1 
model            64K        64K        640K     1 
sybsystemprocs  384K       112K       1280K     2 
pubs2            64K        64K        640K     1 
pubs3            64K        64K        640K     1 
pubtune         160K        96K       1280K     2 
sybsecurity      96K        96K       1280K     2 
dbccdb          112K        96K       1280K     2

See the Reference Manual: Procedures.