SQL Server – Could not allocate space for object in Database

I got the below SQL Database error which primarily states Could not allocate space for object in Database and suggests to deleted unwanted data from the related SQL Server database.

Could not allocate space for object 'dbo.MyTable' in database 'MyDatbase' 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.

After I have analyzed some time found my SQL Database already takes around 10 GB memory size. I am using SQL Server 2008 R2 Expression edition, since it is a free version, it allows only 10 GB maximum size for every single database. Now we should free some space from database by deleting unwanted files after taking backup of unwanted data, so that we can add new data hereafter. I strongly believe, you also getting error due to this reason. So, next step is before deleting data we need to find the tables which are taking high memory space.

SQL query to List Used and Unused Size for all Tables

USE [MorganDB];
GO
SELECT t.NAME AS TableName, p.rows AS RowsCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB Desc

Output: Form the below output you can easily find what are tables are taking high memory space.

Thanks,
Morgan
Software Developer