Shrink Database File

Shrink Database file sql script

This T-SQL script is used to shrink the data file of a SQL Server database. Here is how to use it:

Connect to your SQL Server instance and open a new query window.
Copy the entire script and paste it into the query window.
Change the value of the DECLARE @RUN BIT = 0 line to 1 if you want to run the shrink operation, or 0 if you only want to see the figures.
Replace USE DATABASE_NAME with the name of your database.
The DECLARE @REDUCE_DB_BY INT = 3000 line sets the amount of space you want to reduce the data file by, in MB. Change the value to a different value if desired.
The DECLARE @ONLYSHRINKAMOUNTFREE INT = 1000 line sets the minimum amount of free space required before the shrink operation will be executed. Change the value to a different value if desired.
Execute the script by clicking the "Execute" button or pressing F5.
The script will display the initial file information, the calculated target size of the data file after reducing it, and the file information after the shrink operation is run (if @RUN is set to 1).

Note: Shrinking the data file can cause fragmentation and increase the time it takes to perform data operations. It is generally better to optimize the database design and manage the data file size proactively, rather than relying on shrinking the data file as a solution. Once done an index rebuild can be done to fix the fragmentation.

This script was created to shrink the database file after an excess of 100GB of data was removed. This in turn kept the database file huge on disk with over 85% of free space in the file.

Hope this script can be of some use.

-- PLEASE NOTE THAT THIS WILL CAUSE LOG FILE TO INCREASE!
USE DATABASE_NAME

-- SET TO 1 TO RUN SHRINK, 0 WILL JUST SHOW YOU THE FIGURES 
DECLARE @RUN BIT = 0
DECLARE @REDUCE_DB_BY INT = 3000 -- (MB)reduce by 3000 = 3 GB
DECLARE @ONLYSHRINKAMOUNTFREE INT = 1000 -- only skrink if there is at least 1GB free space available

-- CHECKS
SELECT 
   LogicalName = dbf.name
   ,FileType = dbf.type_desc
   ,FilegroupName = fg.name
   ,PhysicalFileLocation = dbf.physical_name
   ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
   ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
      - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
   ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
      - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf 
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id 
ORDER BY dbf.type DESC, dbf.name;

-- THE SHRINK PROCESS
Declare @name varchar(100)
Declare @filetype varchar(100)
Declare @filesize int
Declare @used int
Declare @freeSpaceMB int

SELECT 
   @name = dbf.name
   ,@filetype = dbf.type_desc
   ,@filesize = CONVERT(int,dbf.size/128.0)
   ,@used = CONVERT(int,dbf.size/128.0 - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
   ,@FreeSpaceMB = CONVERT(int,dbf.size/128.0 - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf 
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id 
Where dbf.type_desc = 'ROWS';

if(@freeSpaceMB > @ONLYSHRINKAMOUNTFREE) 
Begin
   Declare @filesizeReduceTo int = @filesize - @REDUCE_DB_BY -- reduce by 3 GB
   select @name AS 'File Name', @filesize AS 'Current size', @filesizeReduceTo AS 'Reduce to'
      if(@filesizeReduceTo > @used AND @RUN = 1) -- ONLY RUN IF RUN IS SET TO 1
      Begin
         DBCC SHRINKFILE(@name, @filesizeReduceTo);
      End
end

-- SIZES AFTER RUN
SELECT 
   LogicalName = dbf.name
   ,FileType = dbf.type_desc
   ,FilegroupName = fg.name
   ,PhysicalFileLocation = dbf.physical_name
   ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
   ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
      - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
   ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
      - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf 
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id 
ORDER BY dbf.type DESC, dbf.name;

Shrink Database File