Log Truncation and Log shrinking

I see a lot of post in SQL Server forums and groups asking about log truncation and shrinking , when i started my career as DBA this is was on of the main doubt i had , so let me  explain you the difference between log shrinking and log truncation

Log Truncation

We all know that log file is made of multiple Virtual Log Files, transaction log file is a wrap around file, when database is created transactions are logged at the beginning of log file and new log records are added at the end of logical log file towards the end of physical log file ,

So the VLF are reused in a cyclic fashion and the process of making a VLF which is no longer required as free or marking it as reusable is called log truncation

If there is no reusable free space available then Physical log file will expand by adding new VLF if auto growth  is enabled for log file else if  you will get an error 9002 which says that your transaction log is full

When is a log truncated

In SIMPLE recovery model log is truncated by a CHECKPOINT

In FULL and BULK recovery model log is truncated by a LOG BACKUP

So lets have a look at it, i have a database called 'test' it have 10 VLF and i made all the VLF as active by running some transactions , refer the snapshot below , i have used the command dbcc loginfo(  ) to see the the details of VLF inside my log file, here status = '2' means they are active

So why VLF's are not reused ??? Lets check it by using below query

  select log_reuse_wait_desc from sys.databases where name='test'

As the output says its waiting for logbackup to mark VLF's as reusable as i said earlier for a database in full or bulk recovery model a log backup is required to truncate the log file , so here this database is in full recovery model so lets take log backup and see what's going to happen

 BACKUP LOG [test] TO  DISK = N'D:\MSSQL\Backup\test.trn' 

Now lets run dbcc loginfo and could see that 9 VLF is marked as reusable that means log file is truncated
     

Log Shrinking

So log truncation will mark VLF as reusable and this won't reduce the physical size of log file , Shrinking log file is the process of releasing space back to OS from the end of physical log file .

Shrinking  is not suggested to be done frequently until you are sure that your log file won't grow back to the size from which we are shrinking as log growth is an expensive process

So lets see the size of log file of the database 'test' after truncation using the command
dbcc sqlperf(logspace)

Here we can see that log truncation have created free space inside disk so now lets shrink the log file to release unused space back to disk using the below query

USE [test]

GO

DBCC SHRINKFILE (N'test_log' , 0)

GO

now lets check the log file space , you can see that log file have shrink to the initial value