Dear Experts,
Enabled page compression on a big table around 1 TB, and after the compression finished, I was able to see 56 percent free space in the data file.
I was thinking that any new transactions happening on the database would be playing inside this available free space of 56 percent.
Today, i found the disk on which data file resides ran out of disk space.
But, the data file still has that 56 percent free space.
Can someone tell me, why the free space is not getting utilized?
Thanks
Javeed
Index rebuilds - probably. The data file had to grow to account for usage at some point - which filled the drive and now that extra usage is no longer required.
Thanks Jeff,
This is a test environment, I just do adhoc index maintenance there is no scheduled task for this.
Some process required the additional space in the data file - at some point in time. That caused the file to grow and fill the drive.
It can be related to an index rebuild - the page compression - a large data load where the table is then truncated, something else...
It could also be related to the auto growth setting itself. If that is too large - or set to a percentage it could cause the file to grow much more than you think.
Either way - some process triggered the growth and it filled the drive.
Perfect. We performed page compression on few big tables.
We are unable to reclaim the unallocated space back to the OS.
Unallocated space is around 1.5 TB.
Tried shrink on the data file in chunks of 100 MB, but it doesn't release the unallocated space.
What are you doing to shrink the file? What is the command you are executing?
Please be aware that shrinking the file will just cause it to grow again the next time you have a process that needs more space - like an index rebuild. And - once you shrink the file you need to perform an index rebuild because the shrink will fragment all tables in the database.
Might not be worth the time and effort to shrink the file.
I executed this DBCC SHRINKFILE (1, TRUNCATEONLY);
Yes, i am aware of the impact running this, but this is a test system and need to reclaim space.
60% unallocated space is not normal, right?
Using TRUNCATEONLY will only reduce the space used at the end of the file and there is data in the file at the point so it cannot be released. Remove that to shrink the file.
60% may or may not be normal - it all depends on the tables and data contained in the database.
Thanks Jeff.
I am afraid to use NOTRUNCATE, as the volume of pages is huge(some millions of pages), as this will move pages from end and shrink might run forever 
Most of the big tables don't have a CI, just NCI.
I am still looking on how to get back the unallocated space
The file will not shrink if you use the NOTRUNCATE option because there is data at the end of the file.
If you have an issue where there is 'unused' space that is allocated to your tables then you need to perform an ALTER TABLE ... REBUILD on those HEAPS. For HEAPS - deleting (not truncate) data from the table does not deallocate the pages, it just marks them as unusable but still allocated to the table.
The first thing I would do is run through a rebuild of every table - then try NOTRUNCATE to see how much space you can recover. If that doesn't help - then you must shrink the file without that option - and rebuild all tables and indexes again.
Thanks Jeff,
Yes, i rebuilt all the indexes and running the shrink file. Shrink is very very slow.
it has been 24 hours since the shrink started and the unallocated space reduced by 48 GB.
Not sure for 1 TB how long its going to take
You actually need to rebuild the indexes after shrinking the file. I think the problem you have is your HEAPS - and they probably have space allocated.
1 Like