could not allocate a new page for database because of insufficient space in filegroup...

Question

I have an ETL staging which run out of space. I removed some data but tables do not release space after deletion, cannot truncate or copy to another database due to policies/etc.
eventually I want to stay at same space and number of datafiles. there is no need to increase the database size, I can apply retention from now on.
I can either:

1. add new filegroup with one file. then rebuild couple of large tables/cluster indexes on new one, then rebuild all indexes on original filegroup, then rebuild the moved indexes from the new filegroup/file back to original filegroup
2. add space to all datafiles (1 per drive) and rebuild indexes then release unused space from each datafile.

As I see 1. is better, 2. might add fragmentation.

Please let me know your thoughts.

Thank you,

Answer 1

Hi Sehor-8331,

>>could not allocate a new page for database because of insufficient space in filegroup...

This error is usually due to insufficient disk space or the database autogrowth settings have limited the growth( You may have disabled autogrowth and the database size has reached the maximum limit ).You can solve this problem by increasing the disk space or the max size of the database files.Or Delete data from your database to free up space for the new object.

If deleting the table fails to free up space, you can add some space and rebuild the clustered indexes. If the table does not have a clustered index, create a new one.
Rebuilding the clustered index can reduce fragmentation.

===============================================
If the response helped, do "Accept Answer" and upvote it.

Answer 2

but tables do not release space after deletion

The space is released after data deletion, but the database file size will stay as it is; SQL Server don't shrink files on it's own, you have to do it manually using DBCC SHRINKFILE command.