SQL Server系統視圖sys.master_files不能正確顯示資料庫離線狀態

最近發現在SQL Server資料庫（目前測試過SQL Server 2008, 2012，2014，2016各個版本）中，即使資料庫處於離線（OFFLINE）狀態，但是sys.master_files中依然顯示是聯機狀態。本文測試環境為Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 。具體測試過程如下所示：

USE master;

GO

ALTER DATABASE TEST SET OFFLINE WITH ROLLBACK IMMEDIATE;

GO

SELECT  name ,

        physical_name ,

        state ,

        state_desc

FROM    sys.master_files

WHERE   database_id = DB_ID('test');

SELECT  name ,

        state ,

        state_desc

FROM    sys.databases

WHERE   name = 'test';

如上所示，sys.databases系統視圖正確的顯示資料庫處於離線狀態(OFFLINE)，但是系統視圖sys.master_files顯示的依然是聯機（ONLINE），我們可以獲取系統視圖sys.master_files的定義，如下所示（至於如何獲取視圖定義，如果你不清楚，可以參考我的博客SQL Server查看視圖定義總結），

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

 CREATE VIEW sys.master_files AS

    SELECT

        database_id         = f.dbid,

        file_id             = f.fileid,

        file_guid           = f.fileguid,

        type                = f.filetype,

        type_desc           = ft.name,

        data_space_id       = f.grpid,

        name                = f.lname,

        physical_name       = f.pname,

        state               = convert(tinyint, case f.filestate        -- Map enum EMDFileState to AvailablityStates

                                when 0 then 0 when 10 then 0    -- ONLINE

                                when 4 then 7    -- DEFUNCT

                                when 5 then 3 when 9 then 3    -- RECOVERY_PENDING

                                when 7 then 1 when 8 then 1 when 11 then 1    -- RESTORING

                                when 12 then 4    -- SUSPECT

                                else 6 end),    -- OFFLINE

        state_desc          = st.name,

        f.size,

        max_size            = f.maxsize,

        f.growth,

        is_media_read_only  = sysconv(bit, f.status & 8),        -- FIL_READONLY_MEDIA

        is_read_only        = sysconv(bit, f.status & 16),    -- FIL_READONLY

        is_sparse           = sysconv(bit, f.status & 256),    -- FIL_SPARSE_FILE

        is_percent_growth   = sysconv(bit, f.status & 32),    -- FIL_PERCENT_GROWTH

        is_name_reserved    = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending

        create_lsn          = GetNumericLsn(f.createlsn),

        drop_lsn            = GetNumericLsn(f.droplsn),

        read_only_lsn       = GetNumericLsn(f.readonlylsn),

        read_write_lsn      = GetNumericLsn(f.readwritelsn),

        differential_base_lsn     = GetNumericLsn(f.diffbaselsn),

        differential_base_guid    = f.diffbaseguid,

        differential_base_time    = nullif(f.diffbasetime, 0),

        redo_start_lsn            = GetNumericLsn(f.redostartlsn),

        redo_start_fork_guid      = f.redostartforkguid,

        redo_target_lsn           = GetNumericLsn(f.redotargetlsn),

        redo_target_fork_guid     = f.forkguid,

        backup_lsn                = GetNumericLsn(f.backuplsn),

        credential_id             = cr.credential_id

    FROM sys.sysbrickfiles f

    LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate

    LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype

    LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name + N'%' COLLATE database_default

    WHERE f.dbid < 0x7fff -- consistent with sys.databases

        AND f.pruid = 0

        AND f.filestate NOT IN (1, 2)    -- x_efs_Dummy, x_efs_Dropped

        AND has_access('MF', 1) = 1

GO

可以看出sys.master_files的state值來自於系統基表sys.sysbrickfiles的filestate欄位，我們從DAC模式去查看，發現TEST資料庫（dbid=21)的filestat為0，這個值應該為6才對，另外，還有一個讓人意外的是，這個系統表裡面關於TEST資料庫有兩個事務日誌文件記錄，實際上只有一個（其實這個是前陣子寫這篇博客“MS SQL 事務日誌管理小結”時，測試添加、刪除數據事務日誌文件遺留下來的記錄，不清楚是Bug還是什麼問題導致在系統基表還存在這樣的一條記錄）

那麼我們接下來看看sys.sysbrickfiles的具體定義，如下所示：

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

CREATE VIEW sys.databases AS

    SELECT d.name, d.id AS database_id,

        r.indepid AS source_database_id,

        d.sid AS owner_sid,

        d.crdate AS create_date,

        d.cmptlevel AS compatibility_level,