Unable to shrink Data File in SQL Server (Taking too much time)

Question

I am using :

SQL Server 2016 Standard edition

Windows Server 2012 R2 (Standard)

Database Size: 1.4TB

Used space from the Primary Data File: 587GB

Unused space in a Primary Data File : 852GB

Recovery Model: Simple

I am trying to shrink the Data file to 687GB by using a following Command:

USE [TestDB]
GO
DBCC SHRINKFILE (N'TestDB' , 687017) 
GO

And there is no blocking and no other activities are happening on this database.

The above shrink operation is been in the process since last 19hrs and still not completed. So can anyone tell me what needs to be done at this point of time.

How much time should be taken by this shrink operation?

Answer 1

When shrinking a data file is taking forever then what needs to be done at this point of time?

Well you need to do the following things:

1. Rebuild Indexes of a database before performing the shrink operation.
2. If the size of the file is too large for your environment, then try to shrink the file in small chunks.
3. The another option to resolve this issue which I like is emptying a file.

- EMPTYFILE : Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. EmptyFile assures you that no new data will be added to the file. The file can be removed by using the ALTER DATABASE statement.

Example: Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks2012;  
GO  
-- Create a data file and assume it contains data.  
ALTER DATABASE AdventureWorks2012   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = 'C:\t1data.mdf',  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (Test1data, EMPTYFILE);  
GO  
-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  
GO  

That was taken straight from BOL.

How much time should be taken by this shrink operation?

DBCC SHRINKFILE

is a single threaded operation and a single threaded operation does not take advantage of multiple CPUs and have no effect about how much RAM is available.

However; rebuilding indexes before running DBCC SHRINKFILE operations, shrinking file operations will take relatively less time.

Rebuilding Index operations takes advantage of multiple CPUs.

You can also check the DBCC SHRINKFILE completion progress by using the following command:

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <spid running the shrink>;