Sql-server – Static archive database: SQL Server can’t shrink it and leaves ~400G of unallocated space

shrinksql server

We are having an issue with our databases. Here's what happens:

All databases have around 50% of unallocated space and DBCC SHRINKDATABASE/SHRINKFILE won't reclaim the space back. These are archive databases which have been recently cleaned up and are supposed to be read-only, so performance is not an issue (yes, I'm fully aware of shrink and clustered indexes fragmentation and I couldn't care less). The main objective here is to have all datafiles reduced to save $$ on storage space. Again, I don't care about performance, these data files must be reduced at all costs! I'm talking about databases at 800GB, 1,5TB and 2,1TB. So nothing ordinary or small.

So:

  • shrink database shows ~50% of wasted space
  • a post-shrink database with mandatory page reorganization (NOTRUNCATE) and mandatory 1% free space keeps showing with 50%
    unallocated space
  • so an 800 GB database, appears in sp_spaceused as having ~410 GB of unallocated space, even after the shrink
  • a shrink file acknowledged the database has only 1% of free space
  • when we started using SELECT INTO to migrate tables to another database, we started noticing that the original tables were occupying hundreds of thousands of pages, but after migration they were taking no more than a few hundreds of pages
  • DBCC also shows that the original database (before SELECT INTO) uses an huge amount of extents

My analysis of this is the following:

  • shrink is not freeing unallocated space
  • SELECT INTO yes frees up a lot of space

What I would like some help with:

  • why shrink isn't working
  • why SELECT INTO works
  • why the original database has so much wasted space (yes, it had auto-growth enabled for a long time before becoming an archive)

We're already looking at things like fill-factor, page and extents usage, but what we would like to have is at least an explanation for so many lost space and how to reclaim it without SELECT-INTO all terabytes of data. Any help would be appreciated.

Best Answer

There's 2 potential things at play here. Shrink's algo takes the last page and moves it to the first empty spot, this creates massive fragmentation and could also create forwarding records. The better idea is to rebuild your database on a different filegroup using a clustered index that's already defined pre data population.

The other item could be a ghost clean up bug that was around in 2008. I don't know what version you're running, but if it is 2008, ensure you are fully up to date on patches.

https://codingsight.com/moving-existing-table-from-primary-filegroup-to-different-filegroup/

Related Question