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.
Best Answer
According to this documentation (Section:How Does Shrinking the Log File Work?):
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.