After removing the transaction log file in SQL Server, when checking with sys.master_files, it is found that the corresponding transaction log file record information still exists in sys.master_files, except that the state_desc is OFFLINE. It will take some time for this record to disappear from the system view.

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'No growth allowed'

                  WHEN max_size = -1 THEN N'Automatic growth'

              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

In fact, this is because the database of this system view will not be updated in real time, its data is updated asynchronously. The view sys. master_files is something new and is updated asynchronously. It does't update immediately. Previously, due to the asynchronous update of the data of this system view, several problems have been encountered, as shown in the following link:

SQL Server System View sys.master_files does not correctly display the offline state of the database

SQL Server uses sys.master_files to calculate tempdb size incorrectly.

However, it is not clear when the data in sys.master_files will be updated. Sometimes the test results are updated very quickly. Sometimes you can wait several minutes without updating your data. Attached is the test process, in fact Summary of MS SQL Transaction Log Management Here's also a brief introduction.

--Step 1:    First, find a database with two or more transaction logs

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:    View transaction log file details(Including file logical name, physical path, size, growth, etc.)

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'No growth allowed'

                  WHEN max_size = -1 THEN N'Automatic growth'

              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: Make sure that's the primary transaction log file,Because the main log file(primary log)It can't be deleted.

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: View the transaction log status of the corresponding database

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)

--View database transaction log space usage statistics

DBCC SQLPERF(LOGSPACE)

 --Step 6:  View Virtual Logs(Virtual log file (VLF)Transaction log information)

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 transaction log

--A script like this.

BACKUP LOG TEST TO DISK = 'M:\DB_BACKUP\Test.Trn'

GO

--In some cases,Step 6 with Step 7 Cyclic cross-over until transaction log files empty After that, it executes step 8

--Step 8: Remove transaction log files

ALTER DATABASE TEST REMOVE FILE TEST_Log1