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

Yassir 181 Reputation points

I am using :

SQL Server 2017 Standard edition

Windows Server 2016 (Standard)

Database Size: 1.2TB

Used space from the Primary Data File: 300GB

Unused space in a Primary Data File : 874GB

Recovery Model: Simple


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

DBCC SHRINKFILE (N'TestDB' , 614400)

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 9hrs 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?

Any best solution to do operation ?

Thanks for help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,609 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,459 questions
{count} votes

4 answers

Sort by: Most helpful
  1. AniyaTang-MSFT 11,336 Reputation points Microsoft Vendor

    Hi @Yassir

    I found this similar thread for you: https://stackoverflow.com/questions/43510333/unable-to-shrink-data-file-in-sql-server-taking-too-much-time, maybe you can use it as a reference.

    If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

    Best regards,


    0 comments No comments

  2. Olaf Helper 36,111 Reputation points

    The above shrink operation is been in the process since last 9hrs

    Shrinking can take some time, that's normal. Data pages must be rearranged & moved around, before the end of the file can be truncated.

    Shrink the file in smaller steps, e.g. shrink the file always minus 500 MB

    0 comments No comments

  3. Bruce (SqlWork.com) 48,711 Reputation points

    shrinking a db is very expensive. the moving one page must be done as an atomic and logged operation. moving one page requires updating all pages that reference it also. this updates need to committed to log before they can be committed to disk. so your talking millions of random database reads and writes.

    0 comments No comments

  4. Rahul Randive 5,656 Reputation points Microsoft Employee

    Hi @Yassir

    Please refer to a similar thread where you can find a detailed answer.


    Thank you!

    0 comments No comments