Why can't I shrink my logfile below to the requested value?

Experimenting VLFs behaviors with my test lab. Firsty, I have created two databases (BASE2 & BASE3) with different log files size (1024 & 2048 MO) :

Secondly, I'am running the following queries to determine log space usage and the active portion of the VLF :

USE BASE2
dbcc loginfo
GO
USE BASE3
dbcc loginfo
GO

DBCC SQLPERF(LOGSPACE)

So in my opinion there is plenty of space that I can reclaim. Now starting this query to shrink all databases log files :

DECLARE
@db nvarchar(255),
@fileName nvarchar(255),
@cmd nvarchar(255)

BEGIN

DECLARE dbcursor CURSOR
FOR

SELECT table2.name AS BASE, table1.name AS FICHIER_JOURNAL_BASE

    --table1.database_id, 
    --table1.physical_name, 
    --table1.type_desc
FROM sys.master_files AS table1

INNER JOIN sys.databases AS table2 
ON table1.database_id = table2.database_id

WHERE table1.database_id > 4 AND table1.type_desc = 'LOG' 
AND table2.state_desc = 'ONLINE' AND table2.recovery_model_desc = 'FULL';

OPEN dbcursor  
FETCH NEXT FROM dbcursor INTO @db, @fileName;
WHILE (@@FETCH_STATUS <> -1)
BEGIN

--print @db;

 SET @cmd = 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
      PRINT 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
        EXECUTE (@cmd)

FETCH NEXT FROM dbcursor INTO @db, @fileName;
END
CLOSE dbcursor;
DEALLOCATE dbcursor;
END

end here the result :

The requested value was 512MO but the current value on disk is around 650MO. If I restart the query with

USE [BASE2]; DBCC SHRINKFILE ('BASE2_log',8);
USE [BASE3]; DBCC SHRINKFILE ('BASE3_log',8);

the value on disk will be around 250 MO. So why my firt query did not shrink the log files to the requested value 512 MO ? Indeed, There was no active VLF at the end of file and still a lot of unused space.

According to this documentation (Section:How Does Shrinking the Log File Work?):


When any file is shrunk, the space freed must come from the end of the
file. When a transaction log file is shrunk, enough virtual log files
from the end of the log file are freed to reduce the log to the size
requested by the user. The target_size specified by the user is
rounded to the next highest virtual log file boundary.

You have 1GB and 2GB log files which equate to 8 and 16 vlf's of 131,072. You said shrink to 512MB (524,288kb). Since that is on a VLF boundary, it gets rounded up to the next highest VLF boundary (add 131,072) which would be (655,360kb) which is fairly close to the disk space allocation you're seeing.

StackExchange Database Administrators Q#149782, answer score: 3