Insufficient disk space in filegroup

A few days before one of my DBA friend suggested to write a blog on database file growth issues and how to troubleshoot it. 

I liked his suggestion as we DBAs often face space issues in production.

In this blog, we will see what steps and precautions we can take if a database file growth issue is encountered.  

To reproduce the issue below steps have been followed.

1) Created database “Growth”.

2) Added one filegroup named “Growth” and make it default. 

3) Added one NDF named “Growth2.ndf” in the filegroup “Growth” and kept it on another drive.

4) Created Table named “employee_details” in filegroup “Growth”

5) Inserted data into the table and created multiple indexes with low fill factor till the datafile is not getting full. 

Once the data file got full and there is no space available for new data, then the below error got triggered.

The statement has been terminated.

Msg 1101, Level 17, State 12, Line 10

Could not allocate a new page for database 'Growth' because of insufficient disk space in filegroup 'Growth'.

Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup,

or setting autogrowth on for existing files in the filegroup.

 

Completion time: 2022-12-05T19:23:12.5952742+05:30

Screenshot for ref : 

I liked this error message because this error itself has given the solution.

Let’s discuss that below.

Create the necessary space by dropping objects in the filegroup

Delete Table level backups if not required:

                Many developers & DBAs follow the practice to keep table-level backups in the same database. This leads to unnecessary space utilization and also impacts other activities like DB backups, DBCC, etc. 

Unused Indexes:

                Unused indexes, Indexes with low fill factor, and duplicate indexes are leads to high space utilization. I have seen scenarios where the Index size is more than the data size. 

Add additional file in filegroup and keep auto growth on:

                 Add one more data file from the same filegroup and keep it on the drive where free space is available. Make sure auto growth should be on for the newly added data file and auto growth should be off for a previous file.

                If possible, we can move indexes to another filegroup as well. It is the recommended practice to have a separate filegroup for indexes. 

I hope, this information might be helpful. If you know other action items then please let me know. 

Thank You.