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: