SQL Server元數據損壞(metadata corruption)修複

在升級一個SQL Server 2000的資料庫時，遇到了一致性錯誤，其中有幾個錯誤是元數據損壞（metadata corruption），特意研究了一下這個案例，因為以前也零零散散的遇到過一些一致性相關錯誤，但是難得遇到元數據損壞的案例。

如下所示，資料庫從SQL Server 2000還原到SQL Server 2008以後，在做一致性檢查時，發現有元數據損壞（metadata corruption），下麵是實驗是構造的一個測試環境

DBCC CHECKCATALOG (TEST) WITH NO_INFOMSGS;

GO

DBCC CHECKDB(TEST) WITH NO_INFOMSGS;

GO

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (object_id=1362819917) of row (object_id=1362819917,parameter_id=1) in sys.parameters does not have a matching row (object_id=1362819917) in sys.objects.

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (object_id=1362819917) of row (object_id=1362819917,parameter_id=2) in sys.parameters does not have a matching row (object_id=1362819917) in sys.objects.

CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.

CHECKDB found 0 allocation errors and 2 consistency errors in database 'TEST'.

那麼我們先找到系統視圖sys.parameters的數據來源於那個系統基礎表（System Base-Table Metadata），如下腳本所示，我們可以找到sys.parameters 最終來源於sys.syscolpars和 sys.sysobjvalues（關於如何獲取系統視圖定義，此處不做展開分析）

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE VIEW sys.parameters AS

    SELECT object_id, name,

        parameter_id, system_type_id,

        user_type_id, max_length,

        precision, scale,

        is_output, is_cursor_ref,

        has_default_value, is_xml_document,

        default_value, xml_collection_id,

        is_readonly

    FROM sys.parameters$

    WHERE number = 1

GO

CREATE VIEW sys.parameters$ AS

    SELECT c.id AS object_id,

        c.number, c.name,

        c.colid AS parameter_id,

        c.xtype AS system_type_id,

        c.utype AS user_type_id,

        c.length AS max_length,

        c.prec AS precision,

        c.scale AS scale,

        sysconv(bit, c.status & 512) AS is_output,        -- CPM_OUTPUT

        sysconv(bit, c.status & 1024) AS is_cursor_ref,    -- CPM_CURSORREF

        sysconv(bit, isnull(v.objid, 0)) AS has_default_value,

        sysconv(bit, c.status & 2048) AS is_xml_document, -- CPM_XML_DOC        

        v.value AS default_value,

        xmlns AS xml_collection_id,

        sysconv(bit, c.status & 4194304) AS is_readonly -- CPM_IS_READONLY = 0x00400000

    FROM sys.syscolpars c

    LEFT JOIN sys.sysobjvalues v ON v.valclass = 9 AND v.objid = c.id AND v.subobjid = c.colid AND v.valnum = 0    -- SVC_PARAMDEFAULT

    WHERE number > 0 AND has_access('CO', c.id) = 1

但是系統基礎表sys.syscolpars和sys.sysobjvalues在正常情況下是不可見的。只有在資料庫專用管理員連接方式（DAC Dedicated Administrator Connection）連接下才能可見。如下所示，可以判斷數據來源於sys.syscolpars系統基礎表。

此時即使在專用管理員連接下麵也是無法刪除這些數據的，會報“Ad hoc update to system catalogs is not supported”，對應中文提示為“不支持對系統目錄進行即席更新”。如下所示：

EXEC sp_configure 'allow_updates', 1;

RECONFIGURE WITH OVERRIDE;

GO

USE TEST;

GO

DELETE FROM  sys.syscolpars WHERE id=1362819917;

GO

那麼難道就沒有辦法解決這種問題了嗎？ 答案是當然有，不過，這種方式是沒有官方文檔而且也不被官方Support的，如果你要按下麵方法操作，是有一定風險的。所以如果你決定按照下麵方式修複元數據損壞的話，先做好備份。以防萬一。

你必須將資料庫實例在單用戶模式下麵啟動，然後以專用管理員（DAC）連接到資料庫，然後就可以刪除基礎表下麵的數據了，如下截圖所示：

C:\Documents and Settings>net stop mssqlserver

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.

C:\Documents and Settings>net start mssqlserver /m"Microsoft SQL Serve

r Management Studio - Query"

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

USE TEST;

GO

DELETE FROM  sys.syscolpars WHERE id=1362819917;

GO

----------------------------------------------------------------------------------