First off you don’t ever shrink a database file unless you known it won’t need the space back again. (There’s some caveats to that statement but it mostly holds true) If that’s the space the file has consumed generally that’s 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 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 be left 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 @DbFileName SYSNAME;
DECLARE @RowsOrLog VARCHAR(4);
DECLARE @SpaceToLeavePercentage VARCHAR(2);
DECLARE @Sql VARCHAR(MAX);
DECLARE @ShrinkSql VARCHAR(MAX);
DECLARE @NumberOfShrinks INT;
DECLARE @FreeMbRounded INT;
DECLARE @TargetFreeMbRounded INT;
DECLARE @ShrinkIncrementInMb INT;
DECLARE @ShrinkToInMb INT;
DECLARE @TruncateOnly BIT;
/*SET USER INPUT VARIABLES*/
SET @DbName = '_DatabaseName_';
SET @RowsOrLog = 'Rows';
SET @SpaceToLeavePercentage = '10';
SET @NumberOfShrinks = 1;
--SET @TruncateOnly = 1;
IF OBJECT_ID('tempdb..##DbStats') IS NOT NULL
BEGIN
DROP TABLE ##DbStats
END;
IF OBJECT_ID('tempdb..#Check') IS NOT NULL
BEGIN
DROP TABLE #Check
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 #Check (
Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,LogicalName SYSNAME
,FreeMbRounded INT
,TargetFreeMbRounded INT
,DiffMb INT
,ShrinkIncrementInMb INT
,NumberOfShrinksLeft INT
);
IF @SpaceToLeavePercentage IS NULL
BEGIN
SET @SpaceToLeavePercentage = '10'
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
';
INSERT INTO ##DbStats (
LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,DiffMb
)
EXEC (@Sql);
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
) / @NumberOfShrinks
);
UPDATE ##DbStats
SET ShrinkIncrementInMb = @ShrinkIncrementInMb
,NumberOfShrinksLeft = @NumberOfShrinks
WHERE NumberOfShrinksLeft IS NULL;
SELECT TOP 1 *
FROM ##DbStats
ORDER BY Id_DbStats DESC;
IF @ShrinkIncrementInMb > 0
BEGIN
WHILE @NumberOfShrinks > 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 @NumberOfShrinks = @NumberOfShrinks - 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), @NumberOfShrinks) + '
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
';
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
DROP TABLE ##DbStats
END;
--DROP TABLE ##DbStats
/*CHECK TO RUN IN ANOTHER SSMS WINDOW*/
/*
SELECT TOP 1 Id_DbStats
,LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,DiffMb
,ShrinkIncrementInMb
,NumberOfShrinksLeft
FROM ##DbStats WITH (NOLOCK)
ORDER BY Id_DbStats DESC;
/*
/*CHECK PROGRESS*/
/*
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'
*/
*/
/*
DROP TABLE ##DbStats
*/
*/
If you found this post helpful please like, comment and share.
Tidbytez 