2

I've executed report on database "Disk Usage by Table" And I have couple of weird tables, where there are no rows, but disk used couple dozens of gigabytes. What caused such behavior? What to do with this? enter image description here

2

The solution is to TRUNCATE TABLE table_name instead of DELETE FROM table_name.

P.S. This tables are stage table and ETL uses them quite often, every 20 minutes data is inserted and shortly after data was processed - deleted.

2

My guess is that these are heap tables, which can behave in such way. The fact that TRUNCATE TABLE changes the behaviour supports my theory. This is because TRUNCATE TABLE will deallocate the pages/extents (which is your problem in the first place, that they aren't deallocated).

SQL Server has some weaknesses handling heaps, this is one of them. Creating a clustered index will make the table a non-heap and you won't see these issues.

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.