SQL Server 2016 – Table Can’t Grow While 313 MB Still Available

express-editionsql-server-2016

Yesterday, the below error was reported:

Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full

After deletion of some records from the table by an engineer, the error was cleared. I couldn't check the details yesterday as I actually didn't have admin access to it. My access was sorted later on. As I checked the DB size today, I observed the below:

enter image description here

There is 312.63 MB available free space, which means that there is 312.63 MB of space allocated to the database but not yet allocated to any page or objects (Please correct me if I'm wrong). I don't expect the delete operation of yesterday to have released any page/space. So why wasn't the database able to use this space which was readily available and allocated to the database?

I leave out the possibility that the file has grown any further since yesterday because there was ample disk space already available when the the incident occured. This is a SQL Server 2016 SP1 Express Edition which has the Autogrowth setting having been enabled with file growth size of 64 MB and the maximum size being set to Unlimited. Considering:

  10184 MB + 64 MB = 10248 MB > 10240 MB (= 10 GB = maximum allowed DB size in Express Edition)

It's obvious the file couldn't (and can't) grow any further.

While the database couldn't resize the file, it still could have used whatever space that was available to it. So why it didn't happen?

Could it be that, some objects have been dropped after delete was performed yesterday?

Best Answer

You mentioned you don't expect the delete operation to have released space but that is not the case. Pages that are emptied as a result of the delete become unused space (which may be reused for the same object) and emptied extents become unallocated space (which may be used for any object).

Consequently, the DELETE looks to have freed enough space within the data file to avoid the space problem. Consider reorganizing the indexes to further reclaim space from partially filled pages since your database is still over 95% full.

Related Question