DBCC SHRINKFILE
Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.
DBCC SHRINKFILE applies to the files in the current database.
DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE WITH NO_WAIT;
GO
-- Shrink the data file.
DBCC SHRINKFILE (AdventureWorks2012,10);
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL WITH NO_WAIT;
GO
How to check the status / progress of Database Shrink Operation - DBCC SHRINKFILE
Here is the command to show the current status SHRINKFILE Operation
SELECT PERCENT_COMPLETE FROM SYS.DM_EXEC_REQUESTS
WHERE COMMAND = 'DBCCFILESCOMPACT'
