Sql server shrink file progress

Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy. Shrinks the current database's specified data or log file size. You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. You can shrink a file to less than its size at creation, resetting the minimum file size to the new value. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB.

This applies only to empty files that have never contained data. Used pages in the file's area to be freed are moved to free space in the file's kept areas. The free space at the file's end isn't returned to the operating system, and the file's physical size does not change.

The log files are not affected. The data file is shrunk only to the last allocated extent. Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode. You don't have to run the instance of SQL Server in single-user mode to shrink the system databases. The whole log's target size is then translated to each log file's target size. However, if part of the logical log resides in the virtual logs beyond the target size, the Database Engine frees as much space as possible, and then issues an informational message.

The message describes what actions are required to move the logical log out of the virtual logs at the end of the file. 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. This browser is no longer supported. Download Microsoft Edge More info. Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check , we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future. June 4, Pinal Dave. SQL Tips and Tricks. Leave new Niels. Leave a Reply Cancel reply. Exclusive Newsletter Website.