在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_descFROM??? sys.master_files? fWHERE? 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;GOSELECT? f.database_id???????? AS database_id? ,??????? d.name??????????????? AS database_name,??????? f.type_desc?????????? AS type_desc??? ,??????? COUNT(*)????????????? AS log_countFROM??? sys.master_files? fINNER? JOIN sys.databases d ON f.database_id = d.database_idWHERE?? type = 1GROUP?BY f.database_id ,???????? f.type_desc,???????? d.nameHAVING? 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_descFROM??? sys.master_files? fWHERE? 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? fWHERE? f.database_id= DB_ID(@db_name) ? AND? type = 1GROUP?BY f.database_id,f.type_desc;???--Step 4：查看對應數據庫的事務日志狀態?DECLARE @db_name NVARCHAR(32);SET @db_name=N'TEST';SELECT? name ,??????? log_reuse_wait_descFROM??? sys.databasesWHERE name=@db_name????--Step 5: DBCC SQLPERF(LOGSPACE)--查看數據庫的事務日志空間使用情況統計信息DBCC SQLPERF(LOGSPACE)???? --Step 6： 查看虛擬日志情況(虛擬日志文件 (VLF)事務日志的信息)USE TEST;GODBCC LOGINFO('TEST')GO?USE TEST;GODBCC SHRINKFILE('TEST_Log1', 1)??USE TEST;GODBCC SHRINKFILE('TEST_Log1', EMPTYFILE)???--Step 7: 備份事務日志?--類似這樣的腳本。?BACKUP LOG TEST TO?DISK = 'M:\DB_BACKUP\Test.Trn'GO?--有些情況下,Step 6 跟 Step 7要循環交叉進行，直到事務日志文件empty后，然后執行step 8??--Step 8: 移除事務日志文件ALTER?DATABASE TEST REMOVE FILE TEST_Log1

轉載于:https://www.cnblogs.com/kerrycode/p/10559075.html

總結

以上是生活随笔為你收集整理的SQL Server移除事务日志后sys.master_files依然存在记录问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。