在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