1

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.

  • It seems you deleted data from heap tables and free pages weren't deallocated.If so, you should rebuild the tables. You could read more here on the problem. – Denis Rubashkin Mar 10 at 6:55
  • It sounds like fragmentation, have you done reorg/rebuild on your indexes in recent past? for heaps, you need to rebuild them. – Learning_DBAdmin Mar 10 at 7:28
0

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/

| improve this answer | |
0

It turns out it was indeed a case for index REBUILD.

After creating a simple maintenance plan to rebuild everything in the whole database, disregarding their fragmentation level or number of pages (literally everything), after that even SSMS started 'seeing' the newly available free space inside the data file. Shrink file just reduced the 800 GB data file to 80 GB (seriously). Obviously, indexes were fragmented during the shrinking process, so I executed another REBUILD after the shrink, and the data file grew from 80 GB to 100 GB, which is still pretty good when compared to its initial size.

Note: in case you're wondering why the significant reduction, this is an archive database, where production data from the past X years had already been mass deleted by devs, but it was still showing the same total size as the production database (which has 100% of the data).

Microsoft's documentation acknowledges the relatinship between REBUILD and page allocations:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

I've never imagined how poorly maintained indexes could have such a huge impact in terms of disk space allocation.

We're now looking at fill factor and how much it could have also impacted data file size.

Thank you all!

| improve this answer | |
-1

Here you have 2 ways... 1) Check the database file and see if you have empty space to release.. If you have it you can run the shrinkfile to release space (I'll recommend you to shrink in chunks of 1MB) 2) You can run a report that shows you the disk usage by tables on the DB.. If you see that there is a big unused space then you need to see the fill factor per index or if they are heaps.. If that is the case then you need first to rebuild those indexes / heaps and then release the space.

hope this helps!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.