Criando o banco dbccdb

O comando DBCC CHECKSTORAGE do Sybase ASE valida a alocação de páginas e sua consistência.

Todas as informações provenientes deste comando são armazenadas no banco de dados dbccdb, a partir do qual podem ser gerados relatórios de consistência, que podem ser utilizados também para planejamento da capacidade.

Entretanto, a configuração do dbccdb não é trivial. Além de criar um banco de dados, é necessário configurar parâmetros específicos para que o DBCC CHECKSTORAGE possa ser executado, e estes parâmetro diferem para cada banco de dados analisado.

Para ajudar com esta tarefa, existe a procedure sp_plan_dbccdb, que fornece qual o tamanho que o banco dbccdb terá que ter, além de quanta memória e processos devem ser configuradas para cada banco analisado.

No exemplo abaixo, eu crio o dbccdb com o tamanho recomendado, mas executo as configurações apenas para a execução do DBCC CHECKSTORAGE no banco de dados master.

[sybase@nerv00 ~]$ isql -SNERV00 -Usa -w999 -PNerv2011
1> dbcc checkstorage
2> go
Checking master: Logical pagesize is 2048 bytes
Msg 9964, Level 16, State 2:
Server 'NERV00', Line 1:
Database 'dbccdb' was not found. Use sp_helpdb to list the available databases.
1> sp_plan_dbccdb
2> go
Recommended size for dbccdb database is 22MB (data = 20MB, log = 2MB).
No suitable devices for dbccdb in master..sysdevices.
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache comp mem process count
master 192K 48K 640K 0K 1
tempdb 1280K 336K 1280K 0K 2
model 64K 48K 640K 0K 1
pubs2 80K 48K 640K 0K 1
sybsystemdb 96K 48K 1280K 0K 2
sybsystemprocs 1872K 480K 640K 0K 1
sybmgmtdb 928K 240K 640K 0K 1
pubs3 80K 48K 640K 0K 1
(return status = 0)
1> disk init name = "dbccdb_data", physname = "/opt/sybase/data/dbccdb_data.dat", size = "200M"
2> go
1> disk init name = "dbccdb_log", physname = "/opt/sybase/data/dbccdb_log.dat", size = "20M"
2> go
1> create database dbccdb on dbccdb_data = "200M" log on dbccdb_log = "20M"
2> go
CREATE DATABASE: allocating 102400 logical pages (200.0 megabytes) on disk 'dbccdb_data' (102400 logical pages requested).
CREATE DATABASE: allocating 10240 logical pages (20.0 megabytes) on disk 'dbccdb_log' (10240 logical pages requested).
Database 'dbccdb' is now online.
1> exit
[sybase@nerv00 ~]$ isql -SNERV00 -Usa -w999 -PNerv2011 -i/opt/sybase/ASE-15_0/scripts/installdbccdb
(return status = 0)
(1 row affected)
(1 row affected)
Msg 911, Level 11, State 2:
Server 'NERV00', Line 24:
Attempt to locate entry in sysdatabases for database 'dbccalt' by name failed - no entry found under that name. Make sure that name is entered properly.
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.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_config table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_dev_info table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_operation_log table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_operation_results table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_counters table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_faults table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_fault_params table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_exclusions table
Inserting default exclusions into the dbcc_exclusions table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_reco table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
Creating dbcc_control table
Object name has been changed.
Warning: Changing an object or column name could break existing stored procedures, cached statements or other compiled objects.
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
Upgrading dbcc_exclusions schema
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)
[sybase@nerv00 ~]$ isql -SNERV00 -Usa -w999 -PNerv2011
1> sp_cacheconfig "dbccdb_cache", "10M"
2> go
(return status = 0)
1> sp_poolconfig "dbccdb_cache", "2M", "16K"
2> go
(return status = 0)
1> sp_dbcc_createws dbccdb, "default", scan_master, scan, "192K"
2> go
(1 row affected)
Workspace scan_master of 192KB size has been created successfully in dbccdb database.
(return status = 0)
1> sp_dbcc_createws dbccdb, "default", text_master, text, "48K"
2> go
(1 row affected)
Workspace text_master of 48KB size has been created successfully in dbccdb database.
(return status = 0)
1> sp_dbcc_updateconfig master, "max worker processes", "1"
2> go
The value of the dbcc configuration parameter 'max worker processes' for database master has been updated.
(return status = 0)
1> sp_dbcc_updateconfig master, "dbcc named cache", "dbccdb_cache", "2M"
2> go
The value of the dbcc configuration parameter 'dbcc named cache' for database master has been updated.
(return status = 0)
1> sp_dbcc_updateconfig master, "scan workspace", "scan_master"
2> go
The value of the dbcc configuration parameter 'scan workspace' for database master has been updated.
(return status = 0)
1> sp_dbcc_updateconfig master, "text workspace", "text_master"
2> go
The value of the dbcc configuration parameter 'text workspace' for database master has been updated.
(return status = 0)
1> dbcc checkstorage (master)
2> go
Checking master: Logical pagesize is 2048 bytes
(return status = 0)
DBCC CHECKSTORAGE for database 'master' sequence 1 completed at Apr 29 2011 4:01PM. 0 faults and 0 suspect conditions were located. 0 checks were aborted. You should investigate the recorded faults, and plan a course of action that will correct them.
1>

One comment

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.