Normally, you would never want to shrink data files. Not even in case of emergency when running out of space. If you were to shrink the data file, the database would simply grow again and you end up with the same problem. If the database grew, it is because the database needed the space. If it is partially empty, it is because some data was removed, however, it is very likely that it would be needed again so it is a bad idea to shrink it again. If there was an error that caused the database to grow, or there was a change in process, or something extremely rare happened, you might need to shrink a database file. Since shrinking a database will hit performance and possible introduce some locks on the tables, if you must do it, it is better to do it little by little, using a script similar to this below. Remember, after the shrink is done, your indexes will be fragmented, so it is recommended to rebuild all your indexes (and hence the DB will grow a little)
In order to use this script, you should first find if there is space available on the data files, and find the logical name of the data file to shrink.
To do that, run this script first http://www.cookingsql.com/2016/01/data-file-size-and-free-space-one-database/ and use its output to run the script below.
/* DESCRIPTION Shrink a single DATA file, in increments. It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment until it reaches the target free space. CONFIGURATION TOP. Run this script in the database with the file to be shrunk. 1. Make sure to set the USE statement for the correct database 2. Set @DBFileName Enter the Logical File name (not the physical name) 3. Set @TargetFreeMB to the desired file free space in MB after shrink. 4. Set @ShrinkIncrementMB to the increment to shrink file by in MB Compatibility list: MSSQL2005 MSSQL2008/SQL2008R2 MSSQL2012 MSSQL2014 */ SET NOCOUNT ON declare @DBFileName sysname declare @TargetFreeMB int declare @ShrinkIncrementMB int /*------ CONFIGURATION ---------*/ --USE DATABASE -- Set Name of Database file to shrink set @DBFileName = 'AdventureWorks2012_Data2' -- Set Desired file free space in MB after shrink -- Remember, it is not the target file size but how much empty space will be left after the shrink operation -- @SizeMB = @TargetFreeMB + @usedSpace set @TargetFreeMB = 1300 -- Set Increment to shrink file by in MB -- Make sure the @TargetFreeMB + @ShrinkIncrement is bigger than @EmptyDB set @ShrinkIncrementMB = 300 /*------ END CONFIGURATION ---------*/ -- Declare variables declare @sql varchar(8000) declare @SizeMB int declare @EmptyMB int declare @UsedMB int -- Check Input Values IF NOT EXISTS (select name from sysfiles where name = @DBFileName) begin print 'The Data File does not exist on the current database. You either did not set the USE statement or did not specify the Logical File name correctly Terminating Batch' GOTO EOL end select @SizeMB = CAST(size/128.0 AS int) ,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0 ,@EmptyMB = @SizeMB - @UsedMB FROM sys.database_files where name = @DBFileName IF @EmptyMB < @TargetFreeMB BEGIN print 'Cannot proceed with operation because the target free space: ' +cast(@TargetFreeMB as nvarchar(10)) +'MB (user entry) is bigger than the current free space: '+cast(@EmptyMB as nvarchar(10))+'MB Terminating Batch' GOTO EOL END IF @EmptyMB < @TargetFreeMB+@ShrinkIncrementMB BEGIN print 'Cannot proceed with operation because the Shrink Increment: ' +cast(@ShrinkIncrementMB as nvarchar(10)) +'MB (user entry) Plus the Target Free '+cast(@TargetFreeMB as nvarchar(10))+'MB is bigger than the empty Space '+cast(@EmptyMB as nvarchar(10))+'MB Terminating Batch' GOTO EOL END declare @loopExit int set @loopExit = 0 -- Loop until file at desired size while (@SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB) and (@loopExit < 10) begin set @sql = 'dbcc shrinkfile ( '+@DBFileName+', '+convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) WITH NO_INFOMSGS' print 'Start ' + @sql + ' at '+convert(varchar(30),getdate(),121) exec ( @sql ) print 'Done at '+convert(varchar(30),getdate(),121) -- Sets again Original Values select @SizeMB = CAST(size/128.0 AS int) ,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0 ,@EmptyMB = @SizeMB - @UsedMB ,@loopExit = @loopExit+1 FROM sys.database_files where name = @DBFileName end ------------------------------------ IF (@loopExit = 10) BEGIN PRINT 'The SHRINK FILE operation completed 10 loops This could mean that the file is not shrinking or you need to increase the "shrink increment value" Proceed with CAUTION ' GOTO EOL END -- Show Size, Space Used, Unused Space, and Name of all database files SELECT b.name , b.type_desc , CAST(b.size/128.0 AS int) 'SizeMB' , CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB' , ceiling(b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0) 'FreeSpaceMB' , CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%' 'FreeSpace' , CASE WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, ' ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, ' END + CASE WHEN b.max_size = -1 THEN 'unrestricted growth' ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB' END AS Autogrow , cast(b.max_size/128.0 as int) 'MaxSize' , physical_name FROM sys.database_files b EOL: