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

Question

Post reply

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

MrRobot

Old Hand

Points: 368
More actions
November 9, 2023 at 8:12 pm

#4324677
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

DBCC Shrinkfile is indeed a slow operation !

Things that make it even worse:

heaps
(n)text / immage
off row nvarchar(max) / varchar(max) / varbinary(max)

What's the result of this query ?

SELECT session_id
     , request_id
     , percent_complete
     , estimated_completion_time
     , DATEADD(ms, estimated_completion_time, GETDATE()) AS EstimatedEndTime
     , start_time
     , STATUS
 , total_elapsed_time 
 , db_name(database_id) DbName 
 , last_wait_type
 , wait_resource
     , command
-- select *
FROM   sys.dm_exec_requests
WHERE  database_id = db_id('YourDb') ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

MrRobot Old Hand Points: 368 More actions · Answer 2

Thanks for the answer

As bellow the result of query

Capture

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

That doesn't tell much, does it ?

Maybe the SQLSkills blog and refs can give you a clue for the reason you just have to wait until it finishes ...

SQLSkills MEMORY_ALLOCATION_EXT

an article by Erik Darling about a troublesome case HASHBYTES Scalability In SQL Server 2017