How to perform an incremental shrink of a SQL Server database
First off, you should never shrink a database file unless you know it won't need the space back again. (There are some caveats to that statement, but it mostly holds true.) If that is the space the file has consumed, generally that is the space the file needs. Shrinking just means the file will grow again and SQL Server will take a performance hit while it does that. You're better off just adding the additional disk space.
If you need to perform a shrink and you're worried about how long it will take and the performance impact it will have, you can perform shrinks in small chunks. This is good practice, particularly in a production transactional (OLTP) system, as small increments can make continuous, incremental progress instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and allows the shrink process to be interrupted without losing all progress.
The script below can be used to shrink a database file in small increments until it reaches a target free space size based on the current free space percentage.
Simply provide as variables the database name, whether the file to be shrunk is the rows or log file, how much space to leave in terms of a percentage, and how many shrinks you wish to perform. If you only want to truncate the file, set the flag to 1 and you can ignore providing values for the number of shrinks or the percentage of space to keep.
(TruncateOnly means releasing all free space at the end of the file to the operating system without performing any page movement inside the file.)
The script will determine, given the number of shrinks and the target size, what the shrink increment should be. It will then loop and execute the DBCC SHRINKFILE command to shrink the database file by the calculated increment until it reaches the target free space.
Progress updates are written to the global temp table ##DbStats, so you can query this table during the shrink to see how far along the process is. The query to perform this check is commented out at the bottom of the script.
SET NOCOUNT ON;
DECLARE @DbName SYSNAME;
DECLARE @LogicalName VARCHAR(MAX);
DECLARE @DbFileName SYSNAME;
DECLARE @RowsOrLog VARCHAR(4);
DECLARE @SpaceToLeavePercentage VARCHAR(2);
DECLARE @Sql VARCHAR(MAX);
DECLARE @Sql2 VARCHAR(MAX);
DECLARE @ShrinkSql VARCHAR(MAX);
DECLARE @NumberOfShrinks INT;
DECLARE @NumberOfShrinksPre INT;
DECLARE @FreeMbRounded INT;
DECLARE @TargetFreeMbRounded INT;
DECLARE @ShrinkIncrementInMb INT;
DECLARE @ShrinkToInMb INT;
DECLARE @TruncateOnly BIT;
/*SET USER INPUT VARIABLES*/
SET @DbName = 'MY_DATABASE';
SET @RowsOrLog = 'Rows';
SET @SpaceToLeavePercentage = '10';
SET @NumberOfShrinks = 5;
--SET @TruncateOnly = 1;
IF OBJECT_ID('tempdb..##DbStatsCursor') IS NOT NULL
BEGIN
DROP TABLE ##DbStatsCursor
END;
IF OBJECT_ID('tempdb..##DbStatsPre') IS NOT NULL
BEGIN
DROP TABLE ##DbStatsPre
END;
IF OBJECT_ID('tempdb..##DbStats') IS NOT NULL
BEGIN
DROP TABLE ##DbStats
END;
CREATE TABLE ##DbStats (
Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,LogicalName SYSNAME
,FreeMbRounded INT
,TargetFreeMbRounded INT
,DiffMb INT
,ShrinkIncrementInMb INT
,NumberOfShrinksLeft INT
);
CREATE TABLE ##DbStatsPre (
Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,LogicalName SYSNAME
);
CREATE TABLE ##DbStatsCursor (
Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,LogicalName SYSNAME
);
IF @SpaceToLeavePercentage IS NULL
BEGIN
SET @SpaceToLeavePercentage = '10'
END
SET @Sql = 'USE ' + QUOTENAME(@DbName) + '; SELECT Name FROM ' + QUOTENAME(@DbName) + '.sys.database_files WHERE type_desc = ''' + @RowsOrLog + ''''
/*print @Sql; */
INSERT INTO ##DbStatsPre (
LogicalName
)
EXEC (@Sql);
DECLARE THECURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR select LogicalName from ##DbStatsPre order by Id_DbStats desc
OPEN THECURSOR
FETCH NEXT FROM THECURSOR INTO @LogicalName
WHILE @@FETCH_STATUS = 0
BEGIN
set @NumberOfShrinksPre = @NumberOfShrinks;
print '-----------' + @LogicalName;
/* empty in case of loop */
IF OBJECT_ID('tempdb..##DbStats') IS NOT NULL
BEGIN
DELETE FROM ##DbStats;
END;
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,FLOOR(FreeMbRounded) - FLOOR(TargetFreeMbRounded) AS DiffMb
FROM (
SELECT NAME AS LogicalName
,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files
WHERE type_desc = ''' + @RowsOrLog + '''
) AS a
where LogicalName = ''' + @LogicalName + '''
';
INSERT INTO ##DbStats (
LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,DiffMb
)
EXEC (@Sql);
/* select * from ##DbStats; */
INSERT INTO ##DbStatsCursor (LogicalName) values (@LogicalName)
SET @TargetFreeMbRounded = (
SELECT TargetFreeMbRounded
FROM ##DbStats
)
SET @DbFileName = (
SELECT LogicalName
FROM ##DbStats
);
IF @TruncateOnly = 1
BEGIN
SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';
DBCC SHRINKFILE (
N' + '''' + @DbFileName + '''' + '
, 0
, TRUNCATEONLY
) WITH NO_INFOMSGS;
';
EXEC (@ShrinkSql);
END
ELSE
BEGIN
SET @ShrinkIncrementInMb = (
(
SELECT (FLOOR(MAX(FreeMbRounded)) - @TargetFreeMbRounded)
FROM ##DbStats
) / @NumberOfShrinksPre
);
UPDATE ##DbStats
SET ShrinkIncrementInMb = @ShrinkIncrementInMb
,NumberOfShrinksLeft = @NumberOfShrinksPre
SELECT TOP 1 *
FROM ##DbStats
ORDER BY Id_DbStats DESC;
IF @ShrinkIncrementInMb > 0
BEGIN
/* problem?*/
print @NumberOfShrinksPre;
WHILE @NumberOfShrinksPre > 0
BEGIN
SET @FreeMbRounded = (
SELECT FreeMbRounded
FROM ##DbStats
WHERE Id_DbStats = (
SELECT MAX(Id_DbStats)
FROM ##DbStats
)
);
IF @TargetFreeMbRounded > (@FreeMbRounded - @ShrinkIncrementInMb)
BEGIN
SET @ShrinkToInMb = @TargetFreeMbRounded;
END
ELSE
BEGIN
SET @ShrinkToInMb = (@FreeMbRounded - @ShrinkIncrementInMb);
END
SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';
DBCC SHRINKFILE (
N' + '''' + @DbFileName + '''' + '
,' + CONVERT(VARCHAR(12), @ShrinkToInMb) + '
) WITH NO_INFOMSGS;
';
EXEC (@ShrinkSql);
SET @NumberOfShrinksPre = @NumberOfShrinksPre - 1;
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + '
,FLOOR(FreeMbRounded) - ' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + ' AS DiffMb
,' + CONVERT(VARCHAR(255), @ShrinkIncrementInMb) + '
,' + CONVERT(VARCHAR(255), @NumberOfShrinksPre) + '
FROM (
SELECT NAME AS LogicalName
,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files
WHERE type_desc = ''' + @RowsOrLog + ''' and Name = ''' + @LogicalName + '''
) AS a
';
INSERT INTO ##DbStats (
LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,DiffMb
,ShrinkIncrementInMb
,NumberOfShrinksLeft
)
EXEC (@Sql);
SELECT TOP 1 *
FROM ##DbStats
ORDER BY Id_DbStats DESC;
END
END
ELSE
BEGIN
SELECT *
FROM ##DbStats;
END
END;
FETCH NEXT FROM THECURSOR INTO @LogicalName
END
CLOSE THECURSOR
DEALLOCATE THECURSOR
DROP TABLE ##DbStats
DROP TABLE ##DbStatsPreSELECT Id_DbStats ,LogicalName
FROM ##DbStatsPre WITH (NOLOCK)
ORDER BY Id_DbStats asc;
SELECT TOP 1 Id_DbStats
,LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,DiffMb
,ShrinkIncrementInMb
,NumberOfShrinksLeft
FROM ##DbStats WITH (NOLOCK)
ORDER BY Id_DbStats DESC;
SELECT percent_complete AS PercentageComplete
,start_time AS StartTime
,STATUS AS CurrentStatus
,command AS Command
,estimated_completion_time AS EstimatedCompletionTime
,cpu_time AS CpuTime
,total_elapsed_time AS TotalElapsedTime
FROM sys.dm_exec_requests
WHERE Command = 'DbccFilesCompact'
SELECT percent_complete AS PercentageComplete
,start_time AS StartTime
,STATUS AS CurrentStatus
,command AS Command
,estimated_completion_time AS EstimatedCompletionTime
,cpu_time AS CpuTime
,total_elapsed_time AS TotalElapsedTime
FROM sys.dm_exec_requests
WHERE Command = 'DbccSpaceReclaim'
