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:
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?