~楓花雪岳~: [SQL] 模擬資料庫毀損

星期四, 10月 01, 2015

[SQL] 模擬資料庫毀損

要練習 DBCC 資料庫毀損相關議題，第一件事要先學會破壞資料庫，這篇紀錄如何破壞，以 AdventtureWorks2014 HumanResources.Employee 當成破壞目標

查詢 Table 內的 Index_id

SELECT
    T.name AS TableName , 
    I.name AS IndexName ,
    I.index_id ,
    I.type ,
    I.type_desc
FROM sys.indexes AS I
    JOIN sys.tables AS T ON I.object_id = T.object_id
WHERE T.name = 'Employee'

查詢 Index 所在 Page

-- SQL Server 2012 之前版本使用
DBCC IND ([AdventureWorks2014], 'HumanResources.Employee', 1)
GO

-- SQL 2012 開始有此 DMF
-- 該語法故意和 DBCC IND 欄位相對應
SELECT
    allocated_page_file_id AS PageFID
    ,allocated_page_page_id AS PagePID
    ,allocated_page_iam_file_id AS IAMFID
    ,allocated_page_iam_page_id AS IAMPID
    ,object_id AS ObjectID
    ,index_id AS IndexID
    ,partition_id AS PartitionNumber
    ,rowset_id AS PartitionID
    ,allocation_unit_type_desc AS iam_chain_type
    ,page_type AS PageType
    ,page_level AS IndexLevel
    ,next_page_file_id AS NextPageFID
    ,next_page_page_id AS NextPagePID
    ,previous_page_file_id AS PrevPageFID
    ,previous_page_page_id AS PrevPagePID
FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks2014'), OBJECT_ID('HumanResources.Employee'), 1 , NULL, 'DETAILED')
WHERE is_allocated = 1;
GO

sys.dm_db_database_page_allocations DMF 第三個參數為 index_id，分別填入 1 （PK_Employee_BusinessEntityID）和 4 (AK_Employee_LoginID)，就可以查詢 index 所在 Page

PK_Employee_BusinessEntityID，Page 733 為欲破壞 Page，PageType = 1 表示是 Data Page

AK_Employee_LoginID，Page 5133 為欲破壞 Page，PageType = 2 代表是 Index Page

針對 Page 進行破壞

-- 使用 DBCC WRITEPAGE 必須是單一使用者模式
ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- 毀損 PK_Employee_BusinessEntityID 叢集索引，所在的 Page 733
DBCC WRITEPAGE('AdventureWorks2014', 1, 733, 60, 1, 0x00, 1)
-- 毀損 AK_Employee_LoginID 非叢集索引，所在的 Page 5133
DBCC WRITEPAGE('AdventureWorks2014', 1, 5133, 60, 1, 0x00, 1)
-- 切回多人模式
ALTER DATABASE AdventureWorks2014 SET multi_user WITH ROLLBACK IMMEDIATE

執行 DBCC CheckTable，產生的錯誤訊息

DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;
GO

訊息 8939，層級 16，狀態 98，行 76
資料表錯誤: 物件識別碼 1237579447，索引識別碼 1，分割區識別碼 72057594046644224，配置單位識別碼 72057594053197824 (類型 In-row data)，頁面 (1:733)。測試 (IS_OFF (BUF_IOERR, pBUF->bstat)) 失敗。值為 133129 和 -4。
訊息 8928，層級 16，狀態 1，行 76
物件識別碼 1237579447，索引識別碼 1，分割區識別碼 72057594046644224，配置單位識別碼 72057594053197824 (類型 In-row data): 頁面 (1:733) 無法處理。請參閱其他錯誤以取得詳細資料。
訊息 8939，層級 16，狀態 98，行 76
資料表錯誤: 物件識別碼 1237579447，索引識別碼 4，分割區識別碼 72057594052149248，配置單位識別碼 72057594058964992 (類型 In-row data)，頁面 (1:5133)。測試 (IS_OFF (BUF_IOERR, pBUF->bstat)) 失敗。值為 133129 和 -4。
訊息 8928，層級 16，狀態 1，行 76
物件識別碼 1237579447，索引識別碼 4，分割區識別碼 72057594052149248，配置單位識別碼 72057594058964992 (類型 In-row data): 頁面 (1:5133) 無法處理。請參閱其他錯誤以取得詳細資料。
CHECKTABLE 發現了資料表 'HumanResources.Employee' (物件識別碼 1237579447) 中 0 個配置錯誤和 4 個一致性錯誤。
repair_allow_data_loss 是 DBCC CHECKTABLE (AdventureWorks2014.HumanResources.Employee) 所發現之錯誤的最小修復層級。
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

搜尋整個 Table 資料，產生的錯誤訊息

SELECT * FROM HumanResources.Employee

訊息 824，層級 24，狀態 2，行 66
SQL Server 偵測到邏輯的一致性 I/O 錯誤: 不正確的總和檢查碼 (預期: 0x9d61a100; 實際: 0x9d61a18b)。這是當在檔案 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf' 中位移 0x000000005ba000 的資料庫識別碼 7 之頁面 (1:733) 進行讀取的期間所發生的。SQL Server 錯誤記錄檔和系統事件記錄檔中的訊息，或許可以提供其他詳細資訊。這是嚴重的錯誤狀況，且可能會損及資料庫的完整性，所以必須立即更正。請執行完整的資料庫一致性檢查 (DBCC CHECKDB)。會導致這個錯誤的原因有許多可能性; 如需詳細資訊，請參閱《SQL Server 線上叢書》。