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?