1. Support
  2. Knowledge base
  3. 100016466

After performing "Log - Backup Transaction Log" of SQL databases using Backup Exec, the size of the physical log file remains unchanged.

Article: 100016466
Last Published: 2019-07-09
Ratings: 1 0
Product(s): Backup Exec

Problem

After performing "Log - Backup Transaction Log" of SQL databases using Backup Exec, the size of the physical log file remains unchanged.

Solution

Backup Exec provides an option to perform Log - Backup Transaction Log backup of SQL databases which truncates the transaction log on successful completion of the backup, however, the size of the physical log file remains unchanged.
Size reduction of the physical log file is dependent on first truncating the log and then performing a SHRINK operation. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.
 
Transaction Log Physical Architecture
SQL transaction log is a wrap-around file which consists of a number of virtual log files with no fixed size and no fixed number.
While creating or extending log files, SQL decides the file size and its total size is based upon the size of the existing log file and the new file increment.
For example, consider a database with one physical log file divided into five virtual log files as shown in figure 1.
 
Figure 1
 
 
On the creation of a database, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log, which grows toward the end of the physical log.

As a truncation operation occurs, the records in the virtual logs before the minimum recovery log sequence number ( MinLSN) are deleted, this is shown in figure 2.
 
Figure 2
 
 
Similarly, if performing a Log - Backup Transaction Log backup of SQL databases using Backup Exec for Windows Servers, it will not reduce the size of the physical log files.

In order to reduce the size of the physical log file, the following operations have to be performed in SQL:

1. A DBCC SHRINKDATABASE statement is executed  --  https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database
2. A DBCC SHRINKFILE statement referencing a log file is executed  --  https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file
 
 
The following blogs are useful to understand shrinking log files.
 

Was this content helpful?

Article Languages