Shrinking:
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system. Files are always shrunk from the end.
- The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE.
- You cannot shrink the database or transaction log while the database or transaction log is being backed up.
- Conversely, you cannot create a database or transaction log backup while you are trying to shrink the database or transaction log.
Shrinking the Log File or Data file:
Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file.
A shrink-file operation can remove only inactive virtual log files. If no target size is specified, a shrink-file operation removes only the inactive virtual log files beyond the last active virtual log file in the file.
To shrink a database files using SQL Server Management Studio:
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand Databases, and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click files.
Optionally, follwing option are appeared:
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. For log files, displays the current allocated space computed from the output of DBCC SQLPERF(LOGSPACE).
Available free space
For data files, displays the current available free space computed from the output of DBCC SHOWFILESTATS(fileid). For log files, displays the current available free space computed from the output of DBCC SQLPERF(LOGSPACE).
Shrink Action:
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.
Reorganize pages before releasing unused space
Equivalent to executing DBCC SHRINKFILE specifying the target file size. 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. This option allows the file to be dropped using the ALTER DATABASE statement. This option is equivalent to executing DBCC SHRINKFILE with the EMPTYFILE option.
To shrink a database files using Transact-SQL:
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Check percentage of log file using following DBCC and command
DBCC SQLPERF (logspace)
4. Then shrink log file
The SHRINK command itself is very straightforward. To verify the file ID for the log file (usually it’s 2, but for confirmation check sys.database_files) and then issue the command: DBCC SHRINKFILE(2, N), where N is the number of megabytes that you would like to shrink your log to.
USE DatabaseName;
GO
DBCC SHRINKFILE (2);
Go
If having multiple log files in database, provide name of log file and size to shrink file.
USE DatabaseName;
DBCC SHRINKFILE (SecondLogFile, 50);
GO
Shrinking is not possible due to:
The log reuse wait desc shows current status of log file from table sys.databases :
Active transaction:
To resolve this issue you can add log file to another disk and continue to grow, if some transaction are pending and are necessary.
If log size is full and you have no space to backup log and database is not critical, you can use following option:
Set database to simple recovery model and shrink log file and switch back to full.
After switching take full database backup.
LOG BACKUP:
Set database to simple recovery model and shrink log file and switch back to full.
After switching take full database backup.
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (MyDatabase_Log, 5)
GO
ALTER DATABASE MyDatabase SET RECOVERY FULL
GONote: It will break log backup chain if you have log shipping configured.
If you have log shipping configured on database use following option:
Shrinking log shipping database:
1. Manually run job bakup
2. Then shrink
dbcc shrinkfile(2)
Automatic Database Shrinking
When the AUTO_SHRINK database option has been set to ON, the Database Engine automatically shrinks databases that have free space. This option is set using the ALTER DATABASE statement. By default, it is set to OFF. The Database Engine periodically examines the space usage in each database. If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.
After Shrinking:
Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.