Azure SQL DBCC ShrinkDatabase takes too long

Question

Azure SQL DBCC ShrinkDatabase takes too long

RJ 326

Hi there,

I ran DBCC shrinkdatabase in Azure sql provisioned with 10 vcores.

The one DB size is 500GB and other is 800GB. Both ran for 12 hrs and progress was only 30 percent.

Sometimes it gets killed since ETL jobs start to run every 12 hrs.

Anything im doing wrong?

Thanks

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-03T18:33:04.54+00:00

@RJ
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-03T18:33:04.54+00:00

@RJ
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

When database allocated space is in hundreds of gigabytes or higher, shrink might require a significant time to complete, often measured in hours, or days for multi-terabyte databases. Some suggestions to speed up the process:

Rebuild indexes with low page density.
It is recommended to first execute shrink for each data file with the TRUNCATEONLY parameter. This way, if there is any allocated but unused space at the end of the file, it is removed quickly and without any data movement.
If the database has multiple data files, you can speed up the process by shrinking multiple data files in parallel. You do this by opening multiple database sessions, and using DBCC SHRINKFILE on each session with a different file_id value

For more information, read this this documentation.

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-04T17:26:18.2533333+00:00

@RJ
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Joe DeNicola 0 Reputation points

2025-01-02T14:20:25.3066667+00:00

Having the same problem. The database maxsize is 320 GB with only about 120 GB used. And there is only one datafile and one log.

I tried running TRUNCATEONLY first - but DBCC SHRINKDATABASE docs say it does this first anyway.

Runs forever. It gets to about 28% complete and then inches along for hours and hours.

Share via

Azure SQL DBCC ShrinkDatabase takes too long

1 answer

Your answer