SQL Server移除事務日誌後sys.master_files依然存在記錄問題

在SQL Server中移除了事務日誌文件後，使用sys.master_files檢查時發現，對應的事務日誌文件記錄信息依然存在sys.master_files裡面，只是狀態state_desc為OFFLINE。需要經過一段時間，這條記錄在這個系統視圖才會消失。

DECLARE @db_name NVARCHAR(32);

SET @db_name=N'TEST';

SELECT   f.database_id                AS database_id  

        ,DB_NAME(f.database_id)       AS database_name

        ,f.file_id                    AS primary_log_id 

        ,f.name                       AS log_logical_name

        ,f.physical_name              AS database_file_name

        ,f.type_desc                  AS type_desc

        ,CAST(f.size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) 

                                      AS [Size(GB)]  

        ,CASE WHEN max_size = 0  THEN N'不允許增長'

                  WHEN max_size = -1 THEN N'自動增長'

              ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2))

                      + 'G'

         END                          AS max_size 

        ,CASE WHEN is_percent_growth = 1

                 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'

                 ELSE RTRIM(CAST(Growth*8.0/1024 AS CHAR(10))) + 'M'

         END                          AS growth_size 

        ,Is_Percent_Growth            AS IsPercentGrowth 

        ,f.state_desc

FROM    sys.master_files  f

WHERE  f.database_id= DB_ID(@db_name) 

  AND  type = 1

其實這個是因為這個系統視圖的資料庫不會實時更新，它的數據是非同步更新。具體英文描述為： The view sys.master_files is something new and is updated asynchronously. It doesn't updates immediately. 以前也由於這個系統視圖的數據非同步更新，遇到過幾個問題， 如下鏈接所示：

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

SQL Server使用sys.master_files計算tempdb大小不正確。

不過這個系統視圖sys.master_files裡面數據什麼時候更新，確實不清楚它的同步機制。有時候測試實驗發現很快就更新了。有時候可能等好幾分鐘都沒有更新數據。附上測試流程，其實MS SQL 事務日誌管理小結這裡也有介紹，只是略過簡單

--Step 1:    首先找出有2個或多個事務日誌的資料庫

USE master;

GO

SELECT  f.database_id         AS database_id  ,

        d.name                AS database_name,

        f.type_desc           AS type_desc    ,

        COUNT(*)              AS log_count

FROM    sys.master_files  f

INNER  JOIN sys.databases d ON f.database_id = d.database_id

WHERE   type = 1

GROUP BY f.database_id ,

         f.type_desc,

         d.name

HAVING  COUNT(*) >= 2;

--Step 2:    查看事務日誌文件的詳細信息(包括文件邏輯名，物理路徑，大小，增長情況等等)

DECLARE @db_name NVARCHAR(32);

SET @db_name=N'TEST';

SELECT   f.database_id                AS database_id  

        ,DB_NAME(f.database_id)       AS database_name

        ,f.file_id                    AS primary_log_id 

        ,f.name                       AS log_logical_name

        ,f.physical_name              AS database_file_name

        ,f.type_desc                  AS type_desc

        ,CAST(f.size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) 

                                      AS [Size(GB)]  

        ,CASE WHEN max_size = 0  THEN N'不允許增長'

                  WHEN max_size = -1 THEN N'自動增長'

              ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2))

                      + 'G'

         END                          AS max_size 

        ,CASE WHEN is_percent_growth = 1

                 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'

                 ELSE RTRIM(CAST(Growth*8.0/1024 AS CHAR(10))) + 'M'

         END                          AS growth_size 

        ,Is_Percent_Growth            AS IsPercentGrowth 

        ,f.state_desc

FROM    sys.master_files  f

WHERE  f.database_id= DB_ID(@db_name) 

  AND  type = 1

--Step 3: 確認那個是主事務日誌文件,因為主日誌文件（primary log）是不能刪除的

DECLARE @db_name NVARCHAR(32);

SET @db_name=N'TEST';

SELECT  f.database_id            AS database_id  ,

        DB_NAME(f.database_id)   AS database_name,

        MIN(f.file_id)           AS primary_log_id ,

        f.type_desc              AS type_desc    

FROM    sys.master_files  f

WHERE  f.database_id= DB_ID(@db_name) 

  AND  type = 1

GROUP BY f.database_id,f.type_desc;

--Step 4：查看對應資料庫的事務日誌狀態

DECLARE @db_name NVARCHAR(32);

SET @db_name=N'TEST';

SELECT  name ,

        log_reuse_wait_desc

FROM    sys.databases

WHERE name=@db_name

--Step 5: DBCC SQLPERF(LOGSPACE)

--查看資料庫的事務日誌空間使用情況統計信息