ndf data file is out of space

Rishabh Patil 161 Reputation points
2021-08-12T17:17:12.03+00:00

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.

122800-image.png

122770-image.png

Any help would be appreciated

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,013 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-13T20:45:10.787+00:00

    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.)


5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-12T21:22:26.417+00:00

    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.

    0 comments No comments

  2. Rishabh Patil 161 Reputation points
    2021-08-13T01:09:27.777+00:00

    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.

    122933-image.png

    122894-image.png

    0 comments No comments

  3. Seeya Xi-MSFT 16,471 Reputation points
    2021-08-13T05:51:52.847+00:00

    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.

    0 comments No comments

  4. Rishabh Patil 161 Reputation points
    2021-08-13T13:46:37.153+00:00

    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?


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.