This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello everybody,
I searched through the internet and didn't find an answer to this question. Is there a query that can obtain the used space inside a single VLF?
I appreciate your assistance!
Best regards,
Ali
Run the SQL command
DBCC LOGINFO
if you think to get any usefull information for you.
Hi @amomen-8749,
We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Sure Cathyji, thank you. I will close this question shortly. I am still a bit analyzing.
Thanks for your reply. Waiting for your good news.
I guess it could be possible to compute something with help of the output from DBCC LOGINFO and sys.dm_db_log_space_usage. All active VLFs but the current one should be full, and by taking the total size of the active VLFs and deducting the total space used, you get how much is used in that VLF.
Is there any particular reason you want to know this?
My thought as well Erland, though I wasn't sure if VLFs are filled up sequentially. I wrote this:
; with cte as
(select lag(vlf_begin_offset,1,NULL) over (order by vlf_first_lsn asc) as vlf_begin_offset from sys.dm_db_log_info(db_id()) where vlf_active = 1)
select
(select fileproperty(name,'spaceused')/128.0 from sys.database_files where file_id=2)-sum(vlf_size_mb) not_full_vlf_used_space_mb
from cte c join sys.dm_db_log_info(db_id()) l on c.vlf_begin_offset = l.vlf_begin_offset
Can you think of a more intelligent query?
Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
You have no control over VLFs. So what exactly are you trying to do with this information?
I can't see any problems with it, although I like to believe that there could be some simplification.
But I have not given this too much thought, so I can't vouch for that it is correct.
And the question still kind of lingers: why do you want to know this in the first place?
Actually, frankly speaking, my SQL Server database professor asked me to carry out research. I don't have an application for this information in mind. @Tom Phillips @Erland Sommarskog
One question if I may,
Does sql server rearrange data scattered inside log file while shrinking, on need? (It appears to do so especially in simple recovery model where VLFs are truncated automatically without log backup)
Are VLFs written to disk physically sequential on disk or only logically?
Is this a correct assumption to say that always only one semi-full VLF exists and all others are either empty or full?
I have had some observations myself but am not sure. Actually, this information is regarding internals of SQL Server, but I seriously like to know.
I suggest you read this, it describes the processes in detail:
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15
Oh, these academics! :-)
As for your questions, I can't imagine that SQL Server rearranges what is on disk - that sounds dangerous.
For the second question, that depends on what you mean with physical - the file itself may be fragmented in the file system, so it cannot be totally physical.
For the rest of if, I'm too foggy about VLFs myself, so I better stop.
Hi @amomen-8749,
As far as I know, it is impossible for us to monitor the space usage of one VLF. But we can use DBCC SQLPERF(LOGSPACE) to monitor the space usage of the log file. We can also use below T-SQL to get the information about the VLF count and its size.
SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.
Hi @amomen-8749,
Suggest you starting from MS document SQL Server Transaction Log Architecture and Management Guide as Tom mentioned.
Q 1: When the database Transaction Log file is truncated, the truncated space will be freed up and become available for reuse. But the Transaction Log file size will not be decreased, as the truncated space will not be deallocated. On the other hand, the process of recovering the Transaction Log space by deallocating the free VLFs and returning it back to the operating system is called a Transaction Log Shrink. operation.
Q2: Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.
Q3: Yes.
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.