Wednesday 30 December 2009

DBCC Database Installation and configuration

Installing DBCCDB database
Steps 1
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 18MB (data = 16MB, log = 2MB).
Recommended devices for dbccdb are:
Logical Device Name Device Size (KB)
------------------------------ ----------------
systemdbdev 3072
dev_pm_log01 5120
dev_default 20480
dbcc_dev01 30720
dbcc_log_dev01 10240
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2


1> disk init
2> name = 'dbcc_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb.dat'
4> , size = '30M'
5>
1> disk init
2> name = 'dbcc_log_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb_log.dat'
4> , size = '10M'
5>

1> create database dbccdb on dbcc_dev01=30 log on dbcc_log_dev01=10
2>
CREATE DATABASE: allocating 15360 logical pages (30.0 megabytes) on disk 'dbcc_dev01'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk 'dbcc_log_dev01'.
1>

1> use dbccdb
2>
1> sp_addsegment scanseg, dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1> sp_addsegment textseg,dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1>

Install dbccdb tables:
aloncvc0402:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/scripts
aloncvc0402:opctlsy2 >isql -U sa -SGALLARDO -iinstalldbccdb

Database option 'select into/bulkcopy/pllsort' turned ON for database 'dbccdb'.
Running CHECKPOINT on database 'dbccdb' for option 'select
into/bulkcopy/pllsort' to take effect.
Creating dbcc_types table
Object name has been changed.
Creating dbcc_config table
Object name has been changed.
Creating dbcc_dev_info table
Object name has been changed.
Creating dbcc_operation_log table
Object name has been changed.
Creating dbcc_operation_results table
Object name has been changed.
Creating dbcc_counters table
Object name has been changed.
Creating dbcc_faults table
Object name has been changed.
Creating dbcc_fault_params table
Object name has been changed.
Creating dbcc_exclusions table
Inserting default exclusions into the dbcc_exclusions table
Object name has been changed.
Creating dbcc_reco table
Object name has been changed.
Creating dbcc_control table
Object name has been changed.
Inserting type codes into dbcc_types table
Inserting recommendation codes into the dbcc_reco table.
Upgrading dbcc_operation_log schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_faults schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_operation_log schema
Upgrading dbcc_counters schema
Upgrading dbcc_faults schema
Upgrading default exclusion list
Creating index on dbcc_types table
Creating index on dbcc_config table
Creating index on dbcc_dev_info table
Creating index on dbcc_operation_log table
Creating index on dbcc_operation_results table
Creating index on dbcc_counters table
Creating index on dbcc_faults table
Creating index on dbcc_fault_params table
Creating index on dbcc_exclusions table
Creating index on dbcc_reco table
Creating default workspaces
Configuring default workspaces
Installing sp_validatedb
Installing sp_getopid
Installing sp_dbcc_run_recommendations
Installing sp_dbcc_run_alterws
Installing sp_dbcc_run_createws
Installing sp_dbcc_run_configreport
Installing sp_dbcc_run_deletehistory
Installing sp_dbcc_run_deletedb
Installing sp_dbcc_run_evaluatedb
Installing sp_dbcc_run_updateconfig
Installing sp_dbcc_run_summaryreport
Installing sp_dbcc_run_runcheck
Installing sp_dbcc_run_statisticsreport
Installing sp_dbcc_run_faultreport
Installing sp_dbcc_run_differentialreport
Installing sp_dbcc_run_fullreport
Installing sp_dbcc_run_patch_finishtime
Installing sp_dbcc_run_exclusions
Installing dbcc stored procedures.

Installing sp_dbcc_recommendations
Installing sp_dbcc_patch_finishtime
Installing sp_dbcc_exclusions
Installing sp_dbcc_alterws
Installing sp_dbcc_createws
Installing sp_dbcc_configreport
Installing sp_dbcc_deletehistory
Installing sp_dbcc_deletedb
Installing sp_dbcc_evaluatedb
Installing sp_dbcc_updateconfig
Installing sp_dbcc_summaryreport
Installing sp_dbcc_runcheck
Installing sp_dbcc_statisticsreport
Installing sp_dbcc_faultreport
Installing sp_dbcc_differentialreport
Installing sp_dbcc_fullreport
Installing sp_dbcc_help_fault
Loading of dbccdb database is complete.
(return status = 0)

Setting up work tables:
1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ------ ------------------ --------------------------------------------------------------------
dbccdb 40.0 MB sa 31515 Feb 20, 2008 select into/bulkcopy/pllsort
master 89.5 MB sa 1 Feb 19, 2008 mixed log and data
model 3.0 MB sa 3 Feb 19, 2008 mixed log and data
pm 10.0 MB sa 4 Feb 19, 2008 mixed log and data
sag 20.0 MB sa 5 Feb 19, 2008 abort tran on log full
sybsystemdb 3.0 MB sa 31513 Feb 19, 2008 mixed log and data
sybsystemprocs 120.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Feb 20, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1> use dbccdb
2>
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 21MB (data = 19MB, log = 2MB).
dbccdb database already exists with size 40MB.
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
dbccdb 496K 128K 1280K 2
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2

1> sp_dbcc_createws dbccdb,scanseg,scanws,scan,"1M"
2>
(1 row affected)
Workspace scanws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)


1> sp_dbcc_createws dbccdb,textseg,textws,text,"1M"
2>
(1 row affected)
Workspace textws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)
1>
DBCC Configuration parametrs:
1> sp_dbcc_configreport
2>

Reporting default DBCC configuration information.
Parameter Name Value Size
database name DEFAULT_VALUES
scan workspace def$scan$ws (id = 1088003876) 256K
text workspace def$text$ws (id = 1120003990) 128K
(return status = 0)
1>
sp_cacheconfig "dbcc data cache","4M"

sp_poolconfig "dbcc data cache","3M","16K"

1> use master
2>
1> sp_bindcache "dbcc data cache", dbccdb

1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------ ----------- ---------- ----------
dbcc data cache 4.00 Mb 4.00 Mb 0.52 Mb
default data cache 0.00 Mb 8.00 Mb 0.94 Mb
(2 rows affected)

Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
12.01 Mb 4.00 Mb

------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
dbcc data cache dbccdb database V


use dbccdb
sp_dbcc_updateconfig null,"dbcc named cache","dbcc data cache","4M"
sp_dbcc_updateconfig null,"number of woeker processes" , 4

No comments:

Post a Comment