data file shrink taking long time

Question

Hi All,

I am trying shrink a 10TB data file. During the weekend patching window we have planned to shrink the file as it filled up the entire 11TB drive. We have purged some data and we are trying to shrink the file. Doing so, it is taking more time. (i.e. > 9 hours). This is db is in synchronous AG and has some open connections as well now and then.

Couple of questions I have.

1. the total database is 10TB but why it is doing 65TB IO. PFA sp_whoisactive screenshot which i collected like every 5 mins.
2. The progress started at 80% but it took 9 hours to move 1% . Dont know why?
3. What is the best way to shrink the file? take full backup, remove it from AG, change it simple recovery model, shrink the file, once shrink is done, change recovery model to full, take a full backup, restore it all the secondary with norecovery and at it back to AG. Is this a good idea or any other better approaches to handle this.

--Shrink command we have run.

<?query --

DBCC SHRINKFILE (N'db2_dat' , 8388608)

--?>

Regards,

Sam

Answer 1

Shrinkdb is a lot of I/O. It moves a page from the end of the file to a free space. Then all pages with references to this page must be updated, and the allocation table must be updated. All these page changes must be logged. If the database is in use locking can stall it.

After shrink db all indexes are fragmented and should be rebuilt.

removing from AG should have little impact on the shrink, but the shrink causes lots of log records, network traffic and the secondary may fall behind.

you can speed up a shrink by dropping as many indexes as possible, and of course single user mode would prevent any blocking.

Answer 2

Is there LOB data in the database? Shrink + LOB is not a good equation. It can take a very long time to shrink databases with LOB data, because shrink has to trawl those LOB chains again and again.