如何在大型的并且有表分區的資料庫中進行DBCC CHECKDB操作

其實這個問題已經在《SQLSERVER企業級平台管理實踐》裡徐老師已經講過了，不過我想用自己的語言再講詳細一些

筆記連結：筆記19-徐 如何在超大型資料庫上運作DBCC CHECKDB

先來看一下表分區的概念圖

很多時候你或者因為性能問題而使用表分區技術，将一些資料放到不同的分區，而這些資料實際上是被邏輯的放到不同的檔案組裡

大家知道：不管是索引還是資料，檔案組都是這些索引和資料存放的最小邏輯機關

              檔案組是檔案的命名集合，用于簡化資料存放和管理任務（例如，備份和還原操作，檔案組備份和檔案組還原）

 MSDN 使用檔案和檔案組 ：http://msdn.microsoft.com/zh-cn/library/ms187087(v=sql.90).aspx

那麼假如我有一個表使用了表分區技術，如下圖

表中索引和資料放在這3個檔案組中，其中曆史資料/歸檔資料放在檔案組1，索引放在檔案組2，目前資料放在檔案組3

當然，真實環境中的大型資料庫會有更多的檔案組用來存放不同的曆史資料和索引！！

對于使用了分區表機制的資料庫，對于存儲曆史資料的分區檔案組，由于資料本身已經不會發生修改，我們可以把檔案組類型設成隻讀模式，

防止任何誤修改。

步驟一：

我們可以對檔案組1進行一次DBCC  CHECKFILEGROUP檢查，如果沒有錯誤，就将檔案組1設定為隻讀，

這樣以後就不用再執行DBCC  CHECKFILEGROUP檢查了，因為檔案組1是隻讀的，不會再對它進行修改

我們使用下面SQL語句檢查檔案組1是否有錯誤

1 USE [partionTest]
2 GO
3 DBCC CHECKFILEGROUP(1)
4 GO      

1 partionTest的 DBCC 結果。
 2 sys.sysrowsetcolumns的 DBCC 結果。
 3 對象 'sys.sysrowsetcolumns' 的 5 頁中有 552 行。
 4 sys.sysrowsets的 DBCC 結果。
 5 對象 'sys.sysrowsets' 的 1 頁中有 84 行。
 6 sysallocunits的 DBCC 結果。
 7 對象 'sysallocunits' 的 1 頁中有 95 行。
 8 sys.sysfiles1的 DBCC 結果。
 9 對象 'sys.sysfiles1' 的 1 頁中有 6 行。
10 sys.syshobtcolumns的 DBCC 結果。
11 對象 'sys.syshobtcolumns' 的 5 頁中有 552 行。
12 sys.syshobts的 DBCC 結果。
13 對象 'sys.syshobts' 的 1 頁中有 84 行。
14 sys.sysftinds的 DBCC 結果。
15 對象 'sys.sysftinds' 的 0 頁中有 0 行。
16 sys.sysserefs的 DBCC 結果。
17 對象 'sys.sysserefs' 的 1 頁中有 95 行。
18 sys.sysowners的 DBCC 結果。
19 對象 'sys.sysowners' 的 1 頁中有 14 行。
20 sys.sysprivs的 DBCC 結果。
21 對象 'sys.sysprivs' 的 1 頁中有 120 行。
22 sys.sysschobjs的 DBCC 結果。
23 對象 'sys.sysschobjs' 的 1 頁中有 50 行。
24 sys.syscolpars的 DBCC 結果。
25 對象 'sys.syscolpars' 的 7 頁中有 424 行。
26 sys.sysnsobjs的 DBCC 結果。
27 對象 'sys.sysnsobjs' 的 1 頁中有 1 行。
28 sys.syscerts的 DBCC 結果。
29 對象 'sys.syscerts' 的 0 頁中有 0 行。
30 sys.sysxprops的 DBCC 結果。
31 對象 'sys.sysxprops' 的 0 頁中有 0 行。
32 sys.sysscalartypes的 DBCC 結果。
33 對象 'sys.sysscalartypes' 的 1 頁中有 27 行。
34 sys.systypedsubobjs的 DBCC 結果。
35 對象 'sys.systypedsubobjs' 的 1 頁中有 1 行。
36 sys.sysidxstats的 DBCC 結果。
37 對象 'sys.sysidxstats' 的 2 頁中有 151 行。
38 sys.sysiscols的 DBCC 結果。
39 對象 'sys.sysiscols' 的 2 頁中有 263 行。
40 sys.sysbinobjs的 DBCC 結果。
41 對象 'sys.sysbinobjs' 的 1 頁中有 23 行。
42 sys.sysobjvalues的 DBCC 結果。
43 對象 'sys.sysobjvalues' 的 24 頁中有 151 行。
44 sys.sysclsobjs的 DBCC 結果。
45 對象 'sys.sysclsobjs' 的 1 頁中有 20 行。
46 sys.sysrowsetrefs的 DBCC 結果。
47 對象 'sys.sysrowsetrefs' 的 1 頁中有 4 行。
48 sys.sysremsvcbinds的 DBCC 結果。
49 對象 'sys.sysremsvcbinds' 的 0 頁中有 0 行。
50 sys.sysxmitqueue的 DBCC 結果。
51 對象 'sys.sysxmitqueue' 的 0 頁中有 0 行。
52 sys.sysrts的 DBCC 結果。
53 對象 'sys.sysrts' 的 1 頁中有 1 行。
54 sys.sysconvgroup的 DBCC 結果。
55 對象 'sys.sysconvgroup' 的 0 頁中有 0 行。
56 sys.sysdesend的 DBCC 結果。
57 對象 'sys.sysdesend' 的 0 頁中有 0 行。
58 sys.sysdercv的 DBCC 結果。
59 對象 'sys.sysdercv' 的 0 頁中有 0 行。
60 sys.syssingleobjrefs的 DBCC 結果。
61 對象 'sys.syssingleobjrefs' 的 1 頁中有 138 行。
62 sys.sysmultiobjrefs的 DBCC 結果。
63 對象 'sys.sysmultiobjrefs' 的 1 頁中有 102 行。
64 sys.sysdbfiles的 DBCC 結果。
65 對象 'sys.sysdbfiles' 的 1 頁中有 6 行。
66 sys.sysguidrefs的 DBCC 結果。
67 對象 'sys.sysguidrefs' 的 1 頁中有 4 行。
68 sys.sysqnames的 DBCC 結果。
69 對象 'sys.sysqnames' 的 1 頁中有 91 行。
70 sys.sysxmlcomponent的 DBCC 結果。
71 對象 'sys.sysxmlcomponent' 的 1 頁中有 93 行。
72 sys.sysxmlfacet的 DBCC 結果。
73 對象 'sys.sysxmlfacet' 的 1 頁中有 97 行。
74 sys.sysxmlplacement的 DBCC 結果。
75 對象 'sys.sysxmlplacement' 的 1 頁中有 17 行。
76 sys.sysobjkeycrypts的 DBCC 結果。
77 對象 'sys.sysobjkeycrypts' 的 0 頁中有 0 行。
78 sys.sysasymkeys的 DBCC 結果。
79 對象 'sys.sysasymkeys' 的 0 頁中有 0 行。
80 sys.syssqlguides的 DBCC 結果。
81 對象 'sys.syssqlguides' 的 0 頁中有 0 行。
82 sys.sysbinsubobjs的 DBCC 結果。
83 對象 'sys.sysbinsubobjs' 的 0 頁中有 0 行。
84 sys.queue_messages_1977058079的 DBCC 結果。
85 對象 'sys.queue_messages_1977058079' 的 0 頁中有 0 行。
86 sys.queue_messages_2009058193的 DBCC 結果。
87 對象 'sys.queue_messages_2009058193' 的 0 頁中有 0 行。
88 sys.queue_messages_2041058307的 DBCC 結果。
89 對象 'sys.queue_messages_2041058307' 的 0 頁中有 0 行。
90 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038321152，因為它駐留在檔案組 "FileGroup001" (ID 2)中，但未選中該檔案組。  
91 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038386688，因為它駐留在檔案組 "FileGroup002" (ID 3)中，但未選中該檔案組。  
92 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038452224，因為它駐留在檔案組 "FileGroup003" (ID 4)中，但未選中該檔案組。  
93 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038517760，因為它駐留在檔案組 "FileGroup004" (ID 5)中，但未選中該檔案組。  
94 無法處理對象 "aa" (ID 2105058535)、索引 "aa" (ID 0)的行集 ID 72057594038583296，因為它駐留在檔案組 "FileGroup001" (ID 2)中，但未選中該檔案組。  
95 無法處理對象 "rr" (ID 2121058592)、索引 "rr" (ID 0)的行集 ID 72057594038648832，因為它駐留在檔案組 "FileGroup001" (ID 2)中，但未選中該檔案組。  
96 CHECKFILEGROUP 在資料庫 'partionTest' 中發現 0 個配置設定錯誤和 0 個一緻性錯誤。
97 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理者聯系。      

View Code

從結果中可以看到隻檢查了檔案組1裡的錯誤

如果是檢查檔案組2的話就用下面的SQL語句，将1改為2就行了

1 USE [partionTest]
2 GO
3 DBCC CHECKFILEGROUP(2)
4 GO      

1 partionTest的 DBCC 結果。
 2 sys.sysowners的 DBCC 結果。
 3 對象 'sys.sysowners' 的 0 頁中有 0 行。
 4 sys.sysschobjs的 DBCC 結果。
 5 對象 'sys.sysschobjs' 的 0 頁中有 0 行。
 6 sys.syscolpars的 DBCC 結果。
 7 對象 'sys.syscolpars' 的 0 頁中有 0 行。
 8 sys.sysnsobjs的 DBCC 結果。
 9 對象 'sys.sysnsobjs' 的 0 頁中有 0 行。
10 sys.syscerts的 DBCC 結果。
11 對象 'sys.syscerts' 的 0 頁中有 0 行。
12 sys.sysscalartypes的 DBCC 結果。
13 對象 'sys.sysscalartypes' 的 0 頁中有 0 行。
14 sys.systypedsubobjs的 DBCC 結果。
15 對象 'sys.systypedsubobjs' 的 0 頁中有 0 行。
16 sys.sysidxstats的 DBCC 結果。
17 對象 'sys.sysidxstats' 的 0 頁中有 0 行。
18 sys.sysbinobjs的 DBCC 結果。
19 對象 'sys.sysbinobjs' 的 0 頁中有 0 行。
20 sys.sysclsobjs的 DBCC 結果。
21 對象 'sys.sysclsobjs' 的 0 頁中有 0 行。
22 sys.sysremsvcbinds的 DBCC 結果。
23 對象 'sys.sysremsvcbinds' 的 0 頁中有 0 行。
24 sys.sysrts的 DBCC 結果。
25 對象 'sys.sysrts' 的 0 頁中有 0 行。
26 sys.syssingleobjrefs的 DBCC 結果。
27 對象 'sys.syssingleobjrefs' 的 0 頁中有 0 行。
28 sys.sysmultiobjrefs的 DBCC 結果。
29 對象 'sys.sysmultiobjrefs' 的 0 頁中有 0 行。
30 sys.sysguidrefs的 DBCC 結果。
31 對象 'sys.sysguidrefs' 的 0 頁中有 0 行。
32 sys.sysqnames的 DBCC 結果。
33 對象 'sys.sysqnames' 的 0 頁中有 0 行。
34 sys.sysxmlcomponent的 DBCC 結果。
35 對象 'sys.sysxmlcomponent' 的 0 頁中有 0 行。
36 sys.sysxmlplacement的 DBCC 結果。
37 對象 'sys.sysxmlplacement' 的 0 頁中有 0 行。
38 sys.sysasymkeys的 DBCC 結果。
39 對象 'sys.sysasymkeys' 的 0 頁中有 0 行。
40 sys.syssqlguides的 DBCC 結果。
41 對象 'sys.syssqlguides' 的 0 頁中有 0 行。
42 sys.sysbinsubobjs的 DBCC 結果。
43 對象 'sys.sysbinsubobjs' 的 0 頁中有 0 行。
44 sys.queue_messages_1977058079的 DBCC 結果。
45 無法處理對象 "sys.queue_messages_1977058079" (ID 1993058136)、索引 "queue_clustered_index" (ID 1)的行集 ID 72057594037927936，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
46 無法處理對象 "sys.queue_messages_1977058079" (ID 1993058136)、索引 "queue_secondary_index" (ID 2)的行集 ID 72057594037993472，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
47 對象 'sys.queue_messages_1977058079' 的 0 頁中有 0 行。
48 sys.queue_messages_2009058193的 DBCC 結果。
49 無法處理對象 "sys.queue_messages_2009058193" (ID 2025058250)、索引 "queue_clustered_index" (ID 1)的行集 ID 72057594038059008，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
50 無法處理對象 "sys.queue_messages_2009058193" (ID 2025058250)、索引 "queue_secondary_index" (ID 2)的行集 ID 72057594038124544，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
51 對象 'sys.queue_messages_2009058193' 的 0 頁中有 0 行。
52 sys.queue_messages_2041058307的 DBCC 結果。
53 無法處理對象 "sys.queue_messages_2041058307" (ID 2057058364)、索引 "queue_clustered_index" (ID 1)的行集 ID 72057594038190080，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
54 無法處理對象 "sys.queue_messages_2041058307" (ID 2057058364)、索引 "queue_secondary_index" (ID 2)的行集 ID 72057594038255616，因為它駐留在檔案組 "PRIMARY" (ID 1)中，但未選中該檔案組。  
55 對象 'sys.queue_messages_2041058307' 的 0 頁中有 0 行。
56 testPartionTable的 DBCC 結果。
57 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038386688，因為它駐留在檔案組 "FileGroup002" (ID 3)中，但未選中該檔案組。  
58 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038452224，因為它駐留在檔案組 "FileGroup003" (ID 4)中，但未選中該檔案組。  
59 無法處理對象 "testPartionTable" (ID 2073058421)、索引 "testPartionTable" (ID 0)的行集 ID 72057594038517760，因為它駐留在檔案組 "FileGroup004" (ID 5)中，但未選中該檔案組。  
60 對象 'testPartionTable' 的 2 頁中有 126 行。
61 aa的 DBCC 結果。
62 對象 'aa' 的 0 頁中有 0 行。
63 rr的 DBCC 結果。
64 對象 'rr' 的 0 頁中有 0 行。
65 CHECKFILEGROUP 在資料庫 'partionTest' 中發現 0 個配置設定錯誤和 0 個一緻性錯誤。
66 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理者聯系。      

從結果中可以看到隻檢查了檔案組2裡的錯誤

步驟二：

如果發現檔案組1有錯誤，我們需要使用DBCC CHECKDB來修複錯誤或者還原檔案組備份

1 DBCC CHECKDB([partionTest],REPAIR_ALLOW_DATA_LOSS)      

步驟三：設定檔案組1為隻讀檔案組

設定檔案組1為隻讀檔案組之前需要斷開所有對業務資料庫的連接配接

1 USE master
2 GO
3 ALTER DATABASE [partionTest] SET OFFLINE
4 
5 --文法
6 ALTER DATABASE [partionTest] MODIFY FILEGROUP 檔案組名 READONLY
7 
8 ALTER DATABASE [partionTest] MODIFY FILEGROUP FileGroup001 READONLY      

步驟四：對于存儲目前的資料的分區檔案組（不是曆史資料），每個星期或者一星期兩次的DBCC CHECKFILEGROUP即可

因為表中的索引和表中的現有資料是随時變化的，今年2013年還沒有過完，是以檔案組3中的資料和檔案組2中的索引肯定會變化的

這個隻能定期做DBCC CHECKFILEGROUP了

小結：

對于大型資料庫，SQLSERVER針對是否使用了多個檔案組的資料庫提供了比較靈活的DBCC CHECKDB的方法

如果使用了多個檔案組，就使用DBCC CHECKFILEGROUP

注意：這裡除了表分區會用到多個檔案組之外，不用表分區也可以使用多個檔案組，在建立表的時候或者建立索引的時候

可以指定表和索引建立在哪個檔案組上！！

沒有使用表分區技術的資料庫或者隻有一個預設檔案組的資料庫

可以使用下面幾個語句把DBCC CHECKDB裡的關鍵任務分解在每天運作

周一到周三：每天運作一組，假如32張GPOSDB表，32/3=10張表/每天

1 DBCC CHECKTABLE()       

周四： DBCC CHECKALLOC(gposdb) + 一組 DBCC CHECKTABLE()

1 DBCC CHECKALLOC(gposdb) 
2 DBCC CHECKTABLE()      

周五周六：每天運作一組 DBCC CHECKTABLE()

1 DBCC CHECKTABLE()      

周日： DBCC CHECKALLOC(gposdb) + DBCC CHECKCATALOG(gposdb) + 一組DBCC CHECKTABLE()

1 DBCC CHECKALLOC(gposdb) 
2 DBCC CHECKCATALOG(gposdb) 
3 DBCC CHECKTABLE()      

SQLSERVER提供給大家的一些DBCC CHECKDB選項

并行檢查對象

若要限制DBCC檢查可使用的處理器的最大數目，請使用

1 EXEC sys.sp_configure @configname = 'max degree of parallelism', -- varchar(35)
2     @configvalue = 0 -- int      

使用跟蹤辨別 /T 2528 可以禁用并行檢查

PHYSICAL ONLY

對大表使用physical_only可以節省時間，檢查索引,noindex可以讓SQL不用去做費事費力的

非聚集索引檢查

1 DBCC CHECKDB(GPOSDB,NOINDEX) WITH physical_only      

除了DBCC CHECKDB之外,DBCC CHECKFILEGROUP和DBCC CHECKTABLE也有PHYSICAL ONLY和noindex選項

詳細的可以看msdn

CHECKFILEGROUP:http://msdn.microsoft.com/zh-cn/library/ms187332.aspx

CHECKTABLE:http://msdn.microsoft.com/zh-cn/library/ms174338(v=sql.105).aspx

CHECKCATALOG:http://msdn.microsoft.com/zh-cn/library/ms186720(v=sql.105).aspx

CHECKALLOC:http://msdn.microsoft.com/zh-cn/library/ms188422(v=sql.90).aspx

總結

個人感覺其實SQLSERVER的東西挺靈活的，提供的選項也比較多，關鍵是你怎麽去用，你知道他内部的一些原理有多少

就像DBCC CHECKDB這個簡單的指令其實也可以做得很靈活，一些不會用的人對于大型資料庫随便

執行DBCC CHECKDB，結果就是無限期的等待

就像徐老師在《SQLSERVER企業級平台管理實踐》裡說的

根據2009年的經驗，一個大于1TB的資料庫如果沒有錯誤，CHECKDB在某些機器上用20小時就能夠跑完

，而一個有上百上千錯誤的資料庫，哪怕隻有兩三百GB，有可能一天都跑不完。這個差別很顯著

如有不對的地方，歡迎大家拍磚o(∩_∩)o