代码改变世界

SQL Server移除事务日志后sys.master_files依然存在记录问题

2019-03-19 15:38 by 潇湘隐者, ... 阅读, ... 评论, 收藏, 编辑

在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

 

 

clip_image001

 

其实这个是因为这个系统视图的数据库不会实时更新,它的数据是异步更新。具体英文描述为: 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)
--查看数据库的事务日志空间使用情况统计信息
DBCC SQLPERF(LOGSPACE)
 
 
 
 
 --Step 6: 查看虚拟日志情况(虚拟日志文件 (VLF)事务日志的信息)
USE TEST;
GO
DBCC LOGINFO('TEST')
GO
 
USE TEST;
GO
DBCC SHRINKFILE('TEST_Log1', 1)
 
 
USE TEST;
GO
DBCC 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