Truncating logs--will not shrink the physical log file size

FULL recovery model

Before backing up the log, the log file size is 51,200 KB.

After backing up the log, the log file size is 51,200 KB.

What does it mean about the truncating log?

Log truncation does not reduce the size of a physical log size. However, it does reduce the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

To actually shrink the physical log file, you should initiate the one of the following commands:

DBCC SHRINKDATABASE
DBCC SHRINKFILE


Example: DBCC SHRINKDATABASE(AdventureWorks)


To shrink the log file more, issue the following:

DBCC SHRINKFILE (AdventureWorks_log)


Do the Database FULL backup and Differential backup truncate the virtual logs in a physical log file?

Let me do the following exercise:

DECLARE @a int
SET @a=1;
WHILE @a<1000 BEGIN update Person.Address SET AddressLine2='deepcove' + CAST(@a as varchar) WHERE AddressID <10 SET @a=@a+1 ENDNotice the log file size becomes 182,272 KB.

DBCC SHRINKDATABASE(AdventureWorks);

DBCC SHRINKFILE(AdventureWorks_log);

The log file size does not shrink much --from 182,272 KB to 174,080 KB.

Create a log backup
and update some records in the database
RUN DBCCSHRINKFILE(AdventureWorks_log)

It will shrink a lot.

The size of the virtual log file is chosen dynamically by the database engine when log files are created or extended.