Transaction Log noticeable used space after restoring the database from a .bak full backup

Question

Dear everybody!

I have an isolated server and a database on it with no running transactions. First, I performed a full backup of this database, then deleted it and then restored it from the created full backup. When I check for log file’s used space with the following script:

SELECT Name , Size/128.0 AS Size , FILEPROPERTY(Name , 'SpaceUsed') /128.0 AS USed
FROM Sys.Database_Files

The used space is conspicuous and not 0. My question is why this happens? logically I expect it to be 0 or near 0 because there has been no history of transactions. When I use “ApexSQL Log” application which is a wonderful application to view and manage database log file, it brings up no result, but executing the function fn_dblog(NULL, NULL) has some results. For an example to demonstrate this, I used the AdventureWorks2017 database. This is the result for the above SELECT query for this database:

As you can see the log used space is 11.281250 MB which is noticeable. This is relatively larger for bigger databases (I have tested). I also included the result of the following script with this question as a comma delimited file:

select * from fn_dblog(NULL,NULL)

70276-aw2017-fresh-log-csv.txt

Why is there a noticeable used space for the fresh database’s log file? I really appreciate your help.

Answer 1

Why is there a noticeable used space for the fresh database’s log file? I really appreciate your help.

The log is not fresh after database restore. The portion of the log necessary for database recovery is backed up and restored so that the database is recovered in a consistent state.

SQL Server manages space within the transaction log as a series of virtual log files. When the log is truncated, space for the inactive VLFs are freed. There will be at least one active VLF so the used space will be the sum of all active VLFs less the unused space of the last one.

Run this query to see the VLF info in detail.

SELECT * FROM sys.dm_db_log_info(DB_ID());

Answer 2

Hi amomen-8749,

Are there any updates on this issue?
If the inactive VLFs are too much, you can try to shrink the log file.

USE AdventureWorks2017;  
GO  
DBCC SHRINKFILE (AdventureWorks2017_log,targetsize);  
GO  

In addition, here is a query to list active and inactive VLFs which might help.

SELECT [name] AS 'Database Name',
COUNT(li.database_id) AS 'VLF Count',
SUM(li.vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF',
SUM(li.vlf_active*li.vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF',
SUM(li.vlf_size_mb)-SUM(li.vlf_active*li.vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) li
GROUP BY [name]
ORDER BY COUNT(li.database_id) DESC;

Best Regards,
Amelia

Answer 3

Hello Amelia,

Thank you for the query. It was very informative. I read the Microsoft official article regarding the transaction log architecture on the link below:
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15
I also slightly modified your query to include log files used spaces below:

 drop table if exists #temp  
 create table #temp (name nvarchar(128), [LOG Space Used] float)  
 EXEC sp_MSforeachdb 'use ? insert into #temp select DB_NAME(),FILEPROPERTY(Name , ''SpaceUsed'') /128.0 from sys.database_files where file_id = 2'  
   

 SELECT s.name AS 'Database Name',  
 [LOG Space Used],  
 COUNT(li.database_id) AS 'VLF Count',  
 SUM(li.vlf_size_mb) AS 'VLF Size (MB)',  
 SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF',  
 SUM(li.vlf_active*li.vlf_size_mb) AS 'Active VLF Size (MB)',  
 COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF',  
 SUM(li.vlf_size_mb)-SUM(li.vlf_active*li.vlf_size_mb) AS 'Inactive VLF Size (MB)'  
 FROM sys.databases s  
 CROSS APPLY sys.dm_db_log_info(s.database_id) li  
 join #temp dbf  
 on s.name = dbf.name   
 GROUP BY s.name, [LOG Space Used]  
 ORDER BY [LOG Space Used] DESC;  
  

Then I executed the query and got the following result:

As you see the Active VLF Size and LOG used space are slightly relatively correlated. Then I performed some DML operations on the StackOverflow2010 database to fill part of the physical log file and this following new result was achieved:

As you see the used space has remarkably increased, but the Active VLF size remained unchanged. Then I performed a LOG backup to truncate the log but nothing truncated. I guess it was because the used space did not exceed the active VLF size.

With continuing the DML and exceeding the previous active VLF size the following result acquired:

After another LOG backup the following result showed up:

The LOG truncated! This is the "SELECT [Transaction Name],* FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] is not null" result afterwards:

Eventually, if I am to conclude, I would say that the last LOG used space associates with the record of the LOG backup and some other factors pertain to what Microsoft has designed behind the scenes.