0

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?

  • 3
    Just so I understand what you're saying: you think truncating a table drops and recreates the database? > "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 database." – Erik Darling May 20 '17 at 16:36
  • Apologies, I meant drop and create the table. Already edited the question – Carlos Botelho May 21 '17 at 13:13
  • That's different, but still vastly wrong :) – Erik Darling May 21 '17 at 13:20
5

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.

1

You may have to shrink the data file to reclaim the space. Deleting data will not free up the space to the operating system, it will just make the currently used space less full. Truncate is similar to delete but doesn't log everything like a normal delete does.

Here is a dumb analogy I use sometimes: putting a cup on a napkin can take up most of the surface space on the napkin but filling and emptying the cup has no affect on the napkin. In this analogy, the napkin is available space and the cup is your table. In your case you just emptied the cup. Now you have to remove it from the napkin, and shrink will do that for you.

But what you should look into is why this table got so big in the first place. You may very well run into the same problem soon so you may have to revisit the space used and add more space to the database.

  • Hi, Antoine. I think I understand what you mean. But the thing is that in my situation the "SpaceUsed" property for the file doesn't change. It continues indicating that 99.9% of the file is being used. Why it doesn't reduce after table truncation is what I am not understanding. Microsoft docs say truncate frees (deallocates) pages held by the table. Having said that, it is not clear why the spaceUsed property didn't change, the contents of the up itself :-) Can you shed some light over the issue ? – Carlos Botelho May 21 '17 at 16:45
  • My understanding is the page deallocation is not immediate for truncate. Depending on how much data was removed, there could be a delay in when the pages are freed up from when the truncate command was run. – Antoine Hernandez May 21 '17 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.