dbcc checkstorage 檢查數據庫的一致性。在新版本的數據庫中用處比任何一個DBCC命令都大，尤其在數據庫備份前。這篇文章的用意是幫助你：
Ø 掌握dbcc checkstorage命令
Ø 維護dbccdb數據庫
Ø 根據dbccdb生成報告
本文使用的案例中，你將首先由sp_plan_dbccdb的輸出得到運行dbcc checkstorage命令所需資源環境，並在此基礎上規劃資源，建立dbccdb數據庫，最終根據dbccdb數據庫生成報告。
本節練習將基於如下環境：
Ø 系統中存在用戶數據庫aca_db，容量為20MB（數據10MB、日志10MB）
Ø aca_db數據庫占用disk1和disk2兩個設備
Ø aca_db數據庫中有若干個用戶表（auths,article等）
步驟如下：
1、為目標數據庫獲取有關數據庫大小、設備工作空間的大小，高速緩存大小和工作進程數的建議。
sp_plan_dbccdb aca_db
執行結果為：
Recommended size for dbccdb database is 15MB (data = 13MB, 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 process count
aca_db 272K 80K 1280K 2
(return status = 0)
2、如果必要，調整Adaptive Server的工作進程數
3、 為dbcc創建命名高速緩存
配置一個dbcc checkstorage專用的命名高速緩存，不僅可以提高性能並且還可以使數據庫的一致性檢查對其他用戶的影響降到最小。
Dbcc checkstorage要求在命名高速緩存的16k內存池中為每個工作進程准備至少640k的存儲空間，16k內存池的最小容量為推薦的高速緩存容量。
Sp_cacheconfig aca_cache,”3072k”
（注意：重新啟動Adaptive Server才起作用）
4、配置16k的I/O緩存池
sp_poolconfig aca_cache,”2048”,”16k”
使用sp_poolconfig aca_cache命令查看命名高速緩存，結果如下
Cache Name Status Type Config value Run value
---------- ------ ---- ------------ ---------
aca_cache Active Mixed 3.00 Mb 3.00 Mb
IO Size Wash Size Config Size Run Size APF Percent
------- --------- ----------- -------- -----------
2 Kb 204 Kb 0.00 Mb 1.00 Mb 10
16 Kb 400 Kb 2.00 Mb 2.00 Mb 10
4、 如果dbccdb存在，則在創建新的dbccdb數據庫之前，刪除它及其相關的所有設備
use master
go
if exists (select * from master.dbo.sysdatabases
where name = "dbccdb")
begin
print "+++ Dropping the dbccdb database"
drop database dbccdb
end
go
5、 初始化用於dbccdb數據和日志的磁盤設備
disk init
name="dbccdb_dat",
physname="d:\Sybase\example\dbccdata.dat",
vdevno=6,
size=7680
disk init
name="dbccdb_log",
physname="d:\Sybase\example\dbcclog.dat",
vdevno=7,
size=1024
6、 在數據庫設備上創建dbccdb數據庫
create database dbccdb
on dbcc_dat=13
log on dbccdb_log=2
7、 添加磁盤段
use dbccdb
sp_addsegment scanseg,dbccdb,dbccdb_dat
sp_addsegment textseg,dbccdb,dbccdb_dat
8、 填充dbcc數據庫並安裝dbcc存儲過程
執行installdbccdb腳本為dbccdb創建表並初始化dbcc-type表
isql -Usa –P -S -Iiinstalldbccdb
9、 建立和初始化scan和text空間
sp_dbcc_createws dbccdb,scanseg,scan_aca,scan,"272k"
sp_dbcc_createws dbccdb,textseg,text_aca,text,"80k"
10、 更新dbcc_config配置值
use dbccdb
sp_dbcc_updateconfig aca_db,"max worker processes","2"
sp_dbcc_updateconfig aca_db,"dbcc named cache",aca_cache,"3072"
sp_dbc
您正在看的sybase教程是:Sybase dbccdb的安裝配置攻略（陳蘇文）。c_updateconfig aca_db,"scan workspace",scan_aca
sp_dbcc_updateconfig aca_db,"text workspace",text_aca
sp_dbcc_updateconfig aca_db,"OAM count threshold","5"
sp_dbcc_updateconfig aca_db,"IO error abort","3"
sp_dbcc_updateconfig aca_db,"linkage error abort","8"
11、檢查aca_db數據庫
dbcc checkstorage(aca_db)
執行結果為：
Checking aca_db Server Message: Number 9954, Severity 10
Line 1:
Storage checks for 'aca_db' are complete. DBCC is now recording the results in the dbccdb database.
Server Message: Number 9974, Severity 10
Line 1:
DBCC CHECKSTORAGE for database 'aca_db' sequence 1 completed at May 30 2000 10:01AM. 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.
11、 dbccdb生成報告
Ø sp_dbcc_summaryreport在指定的日期或該日期之前為指定的數據庫完成的所有dbcc checkstorage操作
示例：
sp_dbcc_summaryreport
DBCC Operation : checkstorage
(1 row affected)
(return status = 0)
Database Name Start time End Time Operation ID Hard Faults Soft Faults Text Columns Abort Count User Name
------------- ---------- -------- ------------ ----------- ----------- ------------ ----------- ---------aca_db 05/30/2000 10:01:36 10:1:50:503 1 0 0 0 0 sa
Ø sp_dbcc_configreport為目標數據庫生成配置信息的報告。
示例：
sp_dbcc_configreport
Reporting configuration information of database aca_db.
Parameter Name value Size
database name aca_db 20480K
dbcc named cache aca_cache 3072K
text workspace text_aca (id = 544004969) 80K
scan workspace scan_aca (id = 512004855) 272K
OAM count threshold 5%
IO error abort 3
linkage error abort 8
max worker processes 2
Operation sequence number 1
(1 row affected)
(return status = 0)
Ø sp_dbcc_statisticsreport產生從dbcc_counter表中得到的統計信息
示例：
sp_dbcc_statisticsreport "aca_db","auths"
結果：
Parameter Name Index Id value
-------------- -------- -----
count 0 20.0
max size 0 88.0
max count 0 20.0
bytes data 0 1321.0
bytes used 0 1599.0
count 1 1.0
max count 1 1.0
max level 1 0.0
max size 1 11.0
bytes data 1 6.0
bytes used 1 43.0
count 255 0.0
max size 255 0.0
max count 255 0.0
bytes data 255 0.0
bytes used 255 0.0
Parameter Name Index Id Partition
您正在看的sybase教程是:Sybase dbccdb的安裝配置攻略（陳蘇文）。 value Dev_name
-------------- -------- --------- ----- --------
page gaps 0 1 1.0 disk1
pages used 0 1 2.0 disk1
extents used 0 1 1.0 disk1
overflow pages 0 1 0.0 disk1
pages overhead 0 1 1.0 disk1
pages reserved 0 1 6.0 disk1
page extent gaps 0 1 0.0 disk1
ws buffer crosses 0 1 0.0 disk1
page extent crosses 0 1 0.0 disk1
page gaps 1 1 1.0 disk1
pages used 1 1 2.0 disk1
extents used 1 1 1.0 disk1
overflow pages 1 1 0.0 disk1
pages overhead 1 1 1.0 disk1
pages reserved 1 1 6.0 disk1
page extent gaps 1 1 0.0 disk1
/>ws buffer crosses 1 1 0.0 disk1
page extent crosses 1 1 0.0 disk1
page gaps 255 1 1.0 disk1
pages used 255 1 2.0 disk1
extents used 255 1 1.0 disk1
overflow pages 255 1 0.0 disk1
pages overhead 255 1 1.0 disk1
pages reserved 255 1 6.0 disk1
page extent gaps 255 1 0.0 disk1
ws buffer crosses 255 1 0.0 disk1
page extent crosses 255 1 0.0 disk1
Ø sp_dbcc_faultreport報告指定的數據庫對象中發現的在指定日期或該日期之前發生的故障。可以生成短報告，也可以生成長報告。
示例：
sp_dbcc_faultreport 'short'
sp_dbcc_faultreport 'long'
****************************************************
dbcc checkstorage
產生的結果存放在dbccdb數據庫的表裡，如果你不delete history,它會一直保存的
dbcc checkdb,dbcc checkalloc,...
你只有定向輸出到文件裡
如果你在dbcc checkstorage 時，還有對數據庫操作，結果會有歧義的
我現在backup前，只對關鍵的表作dbcc checktable,dbcc tablealloc等
我建議有條件的話，建立一個和生產機一樣的數據庫，將dump的數據庫load到哪裡，做
dbcc checkstorage,如果發現錯誤，修正後，再load回生產機
我現在就這麼干
（freebob）
****************************************************
dbcc checkstorage 命令的執行速度最快，檢查最全面，而且只產生共享鎖，應該是最好的選擇
(chuxu 
****************************************************
我前面配置都差不多，但執行到這裡，怎麼報錯了？
1> dbcc checkstorage(wxjj)
2> go
Checking wxjj: Logical pagesize is 2048 bytes
Msg 9961, Level 17, State 6:
Server 'wxjj', Line 1:
DBCC failed to connect to the requested number of worker processes. The number
of workers configured is insufficIEnt,
or the number of workers exceeds 128. Use
sp_dbcc_updateconfig to reduce 'max worker processes' for this database.
Msg 9960, Level 20, State 1:
Server 'wxjj', Line 1:
A non-recoverable error has occurred in the CHECKSTORAGE Operation. The
Operation has&nb
您正在看的sybase教程是:Sybase dbccdb的安裝配置攻略（陳蘇文）。sp;been aborted.
DBCC CHECKSTORAGE for database 'wxjj' encountered an error. Some results may not
be available. Correct the problems reported and reexecute the CHECKSTORAGE
command.
(bigbug99
*****************************************************
很明顯,你們的worker processes 不足!
(jazy)
*****************************************************
I performed all commands needed for creating dbccdb for pubs2.
Why do I have the following error message?
I used "sp_dbcc_evaluatedb" to check and every thing seemed to be ok.
1> dbcc checkstorage (pubs2)
2> go
Checking pubs2
Msg 9961, Level 17, State 3:
Line 1:
DBCC failed to connect to the requested number of worker processes. The number of workers configured is insufficIEnt, or the number of workers exceeds 128. Use sp_dbcc_updateconfig to reduce 'max worker processes' for this database.
Msg 9960, Level 20, State 1:
Line 1:
A non-recoverable error has occurred in the CHECKSTORAGE operation. The Operation has been aborted.
DBCC CHECKSTORAGE for database 'pubs2' encountered an error. Some results may not be available. Correct the problems reported and reexecute the CHECKSTORAGE command.
1>
2> reset
1> sp_dbcc_evaluatedb
2> go
Recommended values for workspace size, cache size and process count are:
Database name : pubs2
current scan workspace size : 64K suggested scan workspace size : 64K
current text workspace size : 48K suggested text workspace size : 48K
current cache size : 640K suggested cache size : 640K
current process count : 1 suggested process count : 1
(return status = 0)
1>
(jimhongchen)
*****************************************************我執行第一步結果如下，哪位大蝦幫我搞個好的DBCC方案出來？多謝
1> sp_plan_dbccdb wxjj
2> go
Recommended size for dbccdb database is 38MB (data = 36MB, 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 process count
wxjj&
您正在看的sybase教程是:Sybase dbccdb的安裝配置攻略（陳蘇文）。nbsp; 18448K 4624K 4615K 2
(return status = 0)
(bigbug99
*****************************************************
Jazy, thank you.
I solved my problem after your hint.
bigbug99, you can use following command to increase the number of worker process.
1> sp_configure "number of worker processes", 1
2> go
Parameter Name Default
Memory Used Config value Run value
------------------------------ ----------- ----------- ------------ -----------
number of worker processes 0 176 1 1
(1 row affected)
Configuration option changed. Since the option is static, Adaptive Server must be rebooted in order for the change to take effect.
(return status = 0)
1>
(jimhongchen
****************************************************
