How To Shrink SQL Server Log File

Memory allocation is an act of managing database files with consistency so that whenever the requirement of database files occurs they can be invoke and frequently available for performing I/O operations. The SQL database files storage space is partitioned into sequential pages, where page is the unit of SQL Server I/O operation and each page size is 8 kb. It is consist of header whose size is 96 bytes and mainly stores details data of the page like page no, page type, free space covered by page and the object's ID.

In this section we discuss about how to shrink SQL Server transaction log files easily and how to use the unused space. Sometimes the SQL users moves their SQL database pages from one location to another location, in file the unoccupied space comes closer to the front of the file from the end and this makes transaction log file large. But you can reclaim this excess space by shrinking SQL Server log file.

The process of shrinking SQL log files can be executed only if database is online and atleast one Virtual log file is set to free. In some cases, shrinking the log may not be possible until after the next log truncation. As on shrinking of transaction log files the physical size got reduced by removing unused inactive virtual files. Virtual log file is the basic unit of size reduction. For instance you have 400 MB log files that it is divided into four 100 MB virtual files, and the size of the log files reduced in 100 MB only. The size of the file can be reduced to 400 MB to 300 MB but not to 400 MB to 333 MB . Virtual log files are of two types i.e. Active virtual log files and Inactive virtual log files. Active virtual log files hold active log record and they cannot be deleted or removed whereas Inactive virtual log files hold unused space.

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

Shrink SQL Log File In Two Specific Ways

  • Simply using SQL Server Management Studio
  • Through T-SQL Queries

SSMS Steps To Shrink SQL Server Log File

  • Go to Start menu->All Programs-> SQL Server Management Studio->Object Explorer.

ssms objects

  • Click on Databases so that it expands and select the Database which you want to Shrink.
  • Then make a Right Click -> Tasks-> Shrink-> Files.

shrink sql log file

  • A "Shrink Window" will appears, Select File type i.e. Log and moves to downward and select "Reorganize Page before releasing unused page" and choose the Size for Log File.

sql log shrink window

Shrink SQL Log File Through T-SQL Query

DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]

sql log analyzer banner