Sql-server – How to reclaim back space after deleting objects on SQL Server

sql server

I have a database runnin on SQL Server 2008 that is now out of space, errors like "Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. 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." are common.

If I query the FILEPROPERTY('SPACEUSED') for the only file on the PRIMARY filegroup I get less than 10MB which is barely 0.1% space free left.
So, system has some reason in complaining.

I investigated the space occupied by tables by right clicking on database on SSMS, running some reports on disk usage and detected that there was a table consuming lots of space. I fail to understand why, after TRUNCATE ing the table, FILEPROPERTY('SPACEUSED') returns precisely the same (very small) amount. Unless I am mistaken, TRUNCATE works by, under the hood, dropping and recreating the table

The standard reports on disk usage report that table ceased to occupy a big amount of space, though. I expected the FREE space to increase and the ugly "Could not allocate a new page…" message to go away, but not so.

What am I missing here?

Best Answer

I assume the table in question is a heap. You can rebuild the table to reclaim the space, or create a clustered index.

ALTER TABLE dbo.table_name REBUILD;

And/or

CREATE CLUSTERED INDEX cix ON dbo.table_name(column_name);

You can also drop the table and re-create it.

Related Question