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.
sp_plan_dbccdb [dbname]
specifies the name of the target database. If dbname is not specified, sp_plan_dbccdb makes recommendations for all databases in master..sysdatabases.
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
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
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
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.
For information on the dbcc stored procedures for maintaining dbccdb and for generating reports from dbccdb, see Chapter 4, “dbcc Stored Procedures.”
The permission checks for sp_plan_dbccdb differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, any user may execute the procedure. |
Granular permissions disabled |
With granular permissions disabled, you must be the database owner or a user with sa_role. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_dbcc_evaluatedb
