The transaction log file is automatically truncated in simple recovery model and when log backups are performed. A log backup removes the inactive portion of the logs thus freeing up space in log file. It is not recommended to forcefully truncate transaction log file. It may break the log backup chain. However, when absolutely necessary it can be done as shown below. The first one is just to backup up the log file for the reason mentioned above. Selecting a different filegroup type changes the selections in the other fields accordingly.
Filegroup Select a filegroup from the list of Filegroups associated with the selected File type above. Selecting a different filegroup changes the selections in the other fields accordingly. File name Select a file from the list of available files of the selected filegroup and file type. Location Displays the full path to the currently selected file. The path is not editable, but it can be copied to the clipboard.
Currently allocated space For data files, displays the current allocated space. Release unused space Cause any unused space in the files to be released to the operating system and shrink the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. When this option is selected, the user must specify a target file size in the Shrink file to box.
Shrink file to Specifies the target file size for the shrink operation. The size cannot be less than the current allocated space or more than the total extents allocated to the file. Entering a value beyond the minimum or the maximum will revert to the min or the max once the focus is changed or when any of the buttons on the toolbar are clicked.
Empty file by migrating the data to other files in the same filegroup Migrate all data from the specified file. Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent.
You can check the transaction log space usage statistics for all databases using the following T-SQL command:. Also, database best practice recommends to pre-allocate space for transaction log files. This will help to avoid unnecessary auto-growth events. It always happens suddenly in situations when you are urgently in need of free space. This means that the drive, where the SQL transaction log stored, is out of space and SQL cannot write a new transaction data.
You need to backup transaction logs on a productive SQL server regularly to avoid overflowing the transaction log file and automatic growth operations. This situation typically occurs when using a Full recovery model. In this model, the log files cannot be cleared until all transactions are not present in the backup.
It is necessary to ensure you are using a continuous log sequence number LSN in the log records. Accordingly, for the truncate, you need to make a full backup of the DB, or easier and faster temporarily switch it to Simple recovery mode.
Go to Options and switch the database Recovery model to Simple. In File type select Log, in File name field specify the name of the log file. In Shrink action choose Reorganize pages before releasing unused space , set the desired size of the file, and click OK. You can find out the logical name of the log file with the following SQL query:. After you truncated the transaction log and shrinked it, be sure to make a full backup of your database. To automatically switch all databases except system ones into the Simple recovery mode and execute transaction logs shrinking, you can use the following Transact SQL script:.
When you enable this option, the SQL Server will be periodically checking the unused space and reducing the size of the database and log files. Microsoft does not recommend using this option for typical databases, and if you decided to use Auto Shrink, your database should be running in the Full Recovery mode.
0コメント