It does not delete any file. This operation only relocates files. So, it will be helpful after performing a large number of transaction activities that create more log files.
The transaction log can only be shrunken if there is some available space in the log files. It means you can shrink files to a certain limit. After that, it will not be possible to shrink files. The steps to shrink log file in sql server are as follow:. The DBCC commands allow you to perform several operations. Apart from benefits, shrinking transaction log has limitations too.
Some of them are mentioned below:. The shrinking process has various negative consequences. However, it is necessary to perform this process because the growth rate of log files is tremendous. It helps in maintaining a regular space in the system. So, what is the safe way to shrink log file in sql server and avoid their corruption?
It is very simple to use. So, you can download it into your system. It has a date filter that helps you to recover files of a particular period. Log files are created with every transaction in the database. 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. This reduces the file size without moving any data. Optionally, select the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified.
By default, the option is cleared. Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and This option is only available when Reorganize files before releasing unused space is enabled. Optionally, select the Empty file by migrating the data to other files in the same filegroup check box. Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted.
Copy and paste the following example into the query window and click Execute. Skip to main content. Under Shrink action , select Reorganize pages before releasing unused space and click OK.
When the process completes, switch the recovery model back to Full or Bulk-Logged and take a full database backup. If you need to recover disk space from the transaction log file, consider shrinking the log file.
Shrinking recovers space by moving data from the end of the file to unoccupied space at the front of the file. After the process creates enough space at the end of the file, it can be deallocated and returned to the file system. Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file.
0コメント