Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save minhhungit/9718bf5dbf7f9f44f93f7ed9968c5096 to your computer and use it in GitHub Desktop.
Save minhhungit/9718bf5dbf7f9f44f93f7ed9968c5096 to your computer and use it in GitHub Desktop.
Shrink a database file in specified increments down to a specific size or percentage of used space
/*
----------------------------------------------------------------------------
Shrink a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Creation Date: 2020-01-05
Last Update: 2020-08-23
----------------------------------------------------------------------------
Description:
This script uses small intervals to shrink a file (in the current database)
down to a specific size or percentage (of used space).
This can be useful when shrinking files with large heaps and/or LOBs
that can cause regular shrink operations to get stuck.
Change the parameter values below to customize the behavior.
----------------------------------------------------------------------------
!!! DON'T FORGET TO SET THE CORRECT DATABASE NAME !!!
----------------------------------------------------------------------------
Change log:
2020-08-23 - Added new parameters: @AGReplicaLinkedServer, @MaxReplicaRecoveryQueue, @RecoveryQueueSeverity, and @WhatIf
2020-06-22 - Added @RegrowOnError5240 parameter
2020-06-21 - Added @DelayBetweenShrinks and @IterationMaxRetries parameters
2020-03-18 - Added @DatabaseName parameter
2020-01-30 - Added @MinPercentFree, and made all parameters optional
2020-01-05 - First version
----------------------------------------------------------------------------
Parameters:
----------------------------------------------------------------------------
*/
DECLARE
@DatabaseName SYSNAME = NULL -- Leave NULL to use current database context
,@FileName SYSNAME = NULL -- Leave NULL to shrink the file with the highest % free space
,@TargetSizeMB INT = 20000 -- Leave NULL to rely on @MinPercentFree exclusively.
,@MinPercentFree INT = 80 -- Leave NULL to rely on @TargetSizeMB exclusively.
-- Either @TargetSizeMB or @MinPercentFree must be specified.
-- If both @TargetSizeMB and @MinPercentFree are provided, the largest of them will be used.
,@IntervalMB INT = 50 -- Leave NULL to shrink the file in a single interval
,@DelayBetweenShrinks VARCHAR(12) = '00:00:01' -- Delay to wait between shrink iterations (in 'hh:mm[[:ss].mss]' format). Leave NULL to disable delay. For more info, see the 'time_to_execute' argument of WAITFOR DELAY: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/waitfor-transact-sql?view=sql-server-ver15#arguments
,@IterationMaxRetries INT = 3 -- Maximum number of attempts per iteration to shrink a file, when cannot successfuly shrink to desired target size
,@RegrowOnError5240 BIT = 1 -- Error 5240 may be resolved by temporarily increasing the file size before shrinking it again.
,@AGReplicaLinkedServer SYSNAME = NULL -- Linked Server name of the AG replica to check. Leave as NULL to ignore.
,@MaxReplicaRecoveryQueue INT = 10000 -- Maximum recovery queue of AG replica (in KB). Use this to prevent overload on the AG.
,@RecoveryQueueSeverity INT = 16 -- Error severity to raise when @MaxReplicaRecoveryQueue is breached.
,@WhatIf BIT = 0 -- Set to 1 to only print the commands but not run them.
----------------------------------------------------------------------------
-- DON'T CHANGE ANYTHING BELOW THIS LINE --
----------------------------------------------------------------------------
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
SET ANSI_WARNINGS OFF;
DECLARE @CurrSizeMB INT, @StartTime DATETIME, @sp_executesql NVARCHAR(1000), @CMD NVARCHAR(MAX), @SpaceUsedMB INT, @SpaceUsedPct VARCHAR(10), @TargetPct VARCHAR(10);
DECLARE @NewSizeMB INT, @RetryNum INT
SET @DatabaseName = ISNULL(@DatabaseName, DB_NAME());
SET @RetryNum = 0;
IF @DatabaseName IS NULL
BEGIN
RAISERROR(N'Database "%s" was not found on this server.',16,1,@DatabaseName);
GOTO Quit;
END
IF DATABASEPROPERTYEX(@DatabaseName, 'Updateability') <> 'READ_WRITE'
BEGIN
RAISERROR(N'Database "%s" is not writeable.',16,1,@DatabaseName);
GOTO Quit;
END
IF @TargetSizeMB IS NULL AND @MinPercentFree IS NULL
BEGIN
RAISERROR(N'Either @TargetSizeMB or @MinPercentFree must be specified!', 16, 1);
GOTO Quit;
END
IF @IntervalMB < 1
BEGIN
RAISERROR(N'@IntervalMB must be an integer value of 1 or higher (or NULL if you want to shrink using a single interval)', 16,1)
GOTO Quit;
END
SET @sp_executesql = QUOTENAME(@DatabaseName) + '..sp_executesql'
DECLARE @RecoveryQueueCheckCmd NVARCHAR(4000), @RecoveryQueueCheckParams NVARCHAR(4000), @PartnerServer SYSNAME, @RecoveryQueue INT
DECLARE @RecoveryQueueCheckExec NVARCHAR(4000);
IF @AGReplicaLinkedServer IS NOT NULL AND @MaxReplicaRecoveryQueue IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT * FROM sys.servers WHERE server_id > 0 AND name = @AGReplicaLinkedServer)
BEGIN
RAISERROR(N'Specified linked server "%s" was not found.', 16,1, @AGReplicaLinkedServer);
GOTO Quit;
END
IF @RecoveryQueueSeverity IS NULL OR @RecoveryQueueSeverity NOT BETWEEN 0 AND 16
BEGIN
RAISERROR(N'@RecoveryQueueSeverity "%d" is invalid. Must be between an integer 0 and 16.', 16, 1, @RecoveryQueueSeverity);
GOTO Quit;
END
SET @RecoveryQueueCheckParams = N'@DBNAME SYSNAME, @CounterName VARCHAR(1000), @PartnerServer SYSNAME OUTPUT, @CounterValue INT OUTPUT'
SET @RecoveryQueueCheckCmd = N'SELECT @PartnerServer = @@SERVERNAME, @CounterValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE ''%:Database Replica%''
AND counter_name = @CounterName AND instance_name = @DBNAME'
SET @RecoveryQueueCheckExec = QUOTENAME(@AGReplicaLinkedServer) + '.' + @sp_executesql
IF @WhatIf = 1 PRINT @RecoveryQueueCheckCmd;
EXEC @RecoveryQueueCheckExec @RecoveryQueueCheckCmd, @RecoveryQueueCheckParams, @DatabaseName, 'Recovery Queue', @PartnerServer OUTPUT, @RecoveryQueue OUTPUT
IF @RecoveryQueue IS NULL
BEGIN
RAISERROR(N'Unable to fetch "Recovery Queue" for database "%s" via linked server "%s".', 16, 1, @DatabaseName, @AGReplicaLinkedServer);
GOTO Quit;
END
ELSE
BEGIN
RAISERROR(N'Successfully connected to replica server "%s". Current recovery queue for databsae "%s": %d KB.', 0, 1, @PartnerServer, @DatabaseName, @RecoveryQueue) WITH NOWAIT;
END
END
SET @CMD = N'
SELECT TOP 1
@FileName = [name]
,@CurrSizeMB = size / 128
,@SpaceUsedMB = CAST(FILEPROPERTY([name], ''SpaceUsed'') AS int) / 128.0
FROM sys.database_files
WHERE ([name] = @FileName OR @FileName IS NULL)
AND ([size] / 128 > @TargetSizeMB OR @TargetSizeMB IS NULL OR [name] = @FileName)
AND type IN (0,1) -- data and log files only
ORDER BY CAST(FILEPROPERTY([name], ''SpaceUsed'') AS float) / size ASC;'
IF @WhatIf = 1 PRINT @CMD;
EXEC @sp_executesql @CMD, N'@FileName SYSNAME OUTPUT, @CurrSizeMB INT OUTPUT, @SpaceUsedMB INT OUTPUT, @TargetSizeMB INT'
, @FileName OUTPUT, @CurrSizeMB OUTPUT, @SpaceUsedMB OUTPUT, @TargetSizeMB
SET @TargetSizeMB = (
SELECT MAX(val)
FROM (VALUES
(@TargetSizeMB),(CEILING(@SpaceUsedMB / (CAST(@MinPercentFree as float) / 100.0)))
) AS v(val)
)
SET @SpaceUsedPct = CAST( CEILING(@SpaceUsedMB * 100.0 / @CurrSizeMB) as varchar(10)) + '%'
SET @TargetPct = CAST( CEILING(@SpaceUsedMB * 100.0 / @TargetSizeMB) as varchar(10)) + '%'
IF @SpaceUsedMB IS NOT NULL
RAISERROR(N'-- File "%s" current size: %d MB, used space: %d MB (%s), target size: %d MB (%s)',0,1,@FileName,@CurrSizeMB,@SpaceUsedMB,@SpaceUsedPct,@TargetSizeMB,@TargetPct) WITH NOWAIT;
IF @SpaceUsedMB IS NULL OR @CurrSizeMB <= @TargetSizeMB
BEGIN
PRINT N'-- Nothing to shrink'
GOTO Quit;
END
WHILE @CurrSizeMB > @TargetSizeMB
BEGIN
SET @CurrSizeMB = @CurrSizeMB-@IntervalMB
IF @CurrSizeMB < @TargetSizeMB OR @IntervalMB IS NULL SET @CurrSizeMB = @TargetSizeMB
SET @CMD = N'DBCC SHRINKFILE (N' + QUOTENAME(@FileName, N'''') + N' , ' + CONVERT(nvarchar, @CurrSizeMB) + N') WITH NO_INFOMSGS; -- ' + CONVERT(nvarchar(25),GETDATE(),121)
RAISERROR(N'%s',0,1,@CMD) WITH NOWAIT;
IF @WhatIf = 1
PRINT N'-- @WhatIf was set to 1. Skipping execution.'
ELSE
BEGIN
BEGIN TRY
EXEC @sp_executesql @CMD
END TRY
BEGIN CATCH
-- File ID %d of database ID %d cannot be shrunk as it is either being shrunk by another process or is empty.
IF @RegrowOnError5240 = 1 AND ERROR_NUMBER() = 5240
BEGIN
-- This error can be solved by increasing the file size a bit before shrinking again
SET @CMD = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' MODIFY FILE (NAME = ' + QUOTENAME(@FileName, N'''') + N', SIZE = ' + CONVERT(nvarchar, @CurrSizeMB + @IntervalMB) + N'MB); -- ' + CONVERT(nvarchar(25),GETDATE(),121)
PRINT N'-- Error 5240 encountered. Regrowing:'
RAISERROR(N'%s',0,1,@CMD) WITH NOWAIT;
EXEC @sp_executesql @CMD
END
ELSE
THROW;
END CATCH
-- Re-check new file size
EXEC @sp_executesql N'SELECT @NewSizeInMB = [size]/128 FROM sys.database_files WHERE [name] = @FileName;'
, N'@FileName SYSNAME, @NewSizeInMB FLOAT OUTPUT', @FileName, @NewSizeMB OUTPUT
-- See if target size was successfully reached
IF @NewSizeMB > @CurrSizeMB
BEGIN
IF @RetryNum >= @IterationMaxRetries
BEGIN
RAISERROR(N'-- Unable to shrink below %d MB. Stopping operation after %d retries.', 12, 1, @NewSizeMB, @RetryNum) WITH NOWAIT;
BREAK;
END
ELSE
BEGIN
SET @RetryNum = @RetryNum + 1;
SET @CurrSizeMB = @NewSizeMB;
RAISERROR(N'-- Unable to shrink below %d MB. Retry attempt %d...', 0, 1, @NewSizeMB, @RetryNum) WITH NOWAIT;
END
END
ELSE
SET @RetryNum = 0;
-- Sleep between iterations
IF @DelayBetweenShrinks IS NOT NULL
WAITFOR DELAY @DelayBetweenShrinks;
-- Check recovery queue of AG partner
IF @AGReplicaLinkedServer IS NOT NULL AND @MaxReplicaRecoveryQueue IS NOT NULL
BEGIN
SET @RecoveryQueue = NULL;
EXEC @RecoveryQueueCheckExec @RecoveryQueueCheckCmd, @RecoveryQueueCheckParams, @DatabaseName, 'Recovery Queue', @PartnerServer OUTPUT, @RecoveryQueue OUTPUT
IF @RecoveryQueue > @MaxReplicaRecoveryQueue
BEGIN
RAISERROR(N'-- Stopping because the recovery queue in server "%s" has reached %d KB.', @RecoveryQueueSeverity, 1, @PartnerServer, @RecoveryQueue);
GOTO Quit;
END
END
END
END
PRINT N'-- Done - ' + CONVERT(nvarchar(25),GETDATE(),121)
Quit:
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment