I have reached the Sql Server Express edition limit of 10 Go of space per database and for every new INSERT I have this error :
2020-08-10 14:21:58.75 spid67 Could not allocate space for object [Table] in database [DB]
because the 'PRIMARY' filegroup is full.
Create disk space by deleting unneeded files, dropping objects in the filegroup,
adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2020-08-10 14:21:58.75 spid67 CREATE DATABASE or ALTER DATABASE failed
because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
To be sure I checked the remaining space with this query :
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID()
GROUP BY database_id
Which give me :
database_name|log_size_mb|row_size_mb|total_size_mb|
-------------|-----------|-----------|-------------|
DB | 1224.00| 10184.00| 11408.00|
The row_size is my main issue, indeed I have accumulated a lot of data over time in a single table (few millions rows) but I only need to keep 1 year of these data, which is approximately 1/3 of the rows. So I deleted the old rows with a DELETE statement and I later read that it does not free the space instantly. And by running the query above the row_size effectively stays the same. I read a lot about database and file shrinking but it does not seems to be a good idea if the table number of rows will grow again fast which is my case.
My question is : is there a way to quickly fix my error by freeing space and allow my database to be able to insert new rows ? My need is to allow again new inserts in this database after deleting records of my huge table.
Note: I don't want to switch to another server with more space or to the standard edition. Because the express edition is sufficient for my use case, the table will grow fast again but I want to limit that situation happening again later.
EDIT: my table doesn't seems to be a heap because it contains in sys.partitions one index of type "Clustered Index/b-tree".
