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

Question

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

Answer 1

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,

Aniya

Answer 2

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

Answer 3

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.

Answer 4

Hi @Yassir

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

https://learn.microsoft.com/en-us/answers/questions/1353942/shrinking-a-large-data-file

Thank you!