ndf data file is out of space

Question

Hi Everyone,

I am looking into one ETL server out of space issue in which one data file (ndf ) of the db is showing full.

I tried index rebuild but it fails with error Could not allocate a new page for database 'RptsStgLive' because of insufficient disk space in filegroup 'Staging'.

DBCC Shrinkfile does not do anything too

I have no idea what should I do further to resolve this; please see below screenshot to understand the problem.

Any help would be appreciated

Answer 1

You have only talked about files, and not detailed whether they are part of the same filegroup, each file is its own filegroup. But the error message in combination with the names of the files and the uneven space usage, makes me believe that you have four different filegroups, PRIMARY, Staging, Index and Data.

Staging is full, but you have some space to spare in Data. You can move one or more big tables from Staging to Data with:

CREATE CLUSTERED INDEX indexname ON tbl(col1, col2, ...)
WITH (DROP_EXISTING = ON)
ON Data

For tables that are heaps (which is maybe not that unlikely in something called staging), you would have to create a clustered index and then drop it. Drop any non-clustered index before you do this. Or just create a new table and copy data over and drop the old table and rename.

You talked about indexes with 50% fill-factor. Yes, there may be some space to gain - but that only helps you if they are located in the Staging filegroup. If they are in the indexes filegroup, that is not going to help much.

A more radical idea, which is more work, but which will give you more flexibility is to restore the database on a different server with plenty of free disk space and move everything to the Data filegroup, and then drop the Staging and Indexes filegroups. Then backup the database again and move it back to the original server.

And then there is the solution with least amount of work: Get more disk space. (At least technical work; I don't know much local red tape you will have to get through, to get that disk space.)

Answer 2

Since it says that there is 0 MB free on the disk, you need to find an other disk where you can add more data files. Alternatively, extend h: to have more space.

Answer 3

Hi Erland,

Could you please look at the below screenshot and see if I have room to adjust the space from the different file's available space?

Or perhaps I may need to change some growth parameters?

Kindly advise.

Answer 4

Hi @MeRishabh-5801，

If your log grows normally, as Erland said,

you need to find an other disk where you can add more data files.

If it is not normal growth, it is necessary to find the reason for the sudden increase.
DBCC Shrinkfile cannot solve the fundamental problem.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

I have found that there are Indexes in this database whose sizes are actually 4 times data size.
One thing I note that filfactor is 50 for them, can I rebuild them with 90 filfactor if yes then how because I probably get insufficient disk space error.

Any thoughts please?