SQL Server移除事务日志后sys.master_files依然存在记录问题
栏目: 数据库 · SQL Server · 发布时间: 5年前
在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) --查看数据库的事务日志空间使用情况统计信息 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
以上所述就是小编给大家介绍的《SQL Server移除事务日志后sys.master_files依然存在记录问题》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Linux Device Drivers
Jonathan Corbet、Alessandro Rubini、Greg Kroah-Hartman / O'Reilly Media / 2005-2-17 / USD 39.95
Device drivers literally drive everything you're interested in--disks, monitors, keyboards, modems--everything outside the computer chip and memory. And writing device drivers is one of the few areas ......一起来看看 《Linux Device Drivers》 这本书的介绍吧!