How to determine why a Table is using so much space?

Question

I've encountered a very weird table which I can not understand. It Consists of just four Columns, two ints and two varchar(64). Only 1000 rows are contained, but the storage used is over 6GB! There is no tracking enabled and Index space is lenn than 1MB.

How can I find out why this thing is consumping that much space and how would I be able to fix this?

EDIT: sp_spaceused result

Table1  1264 8642024KB  6916872KB   552KB   1724600KB

Did you delete many rows from trust table? What if you create a new take with the same structure and copy the date there? — Dr Phil, Jul 24 '19 at 07:40
Properties -> Storage or using https://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d — Jaster, Jul 24 '19 at 07:42
Try to run the command: `ALTER TABLE [YourWeirdTableName] REBUILD;` and then check its size again. — Denis Rubashkin, Jul 24 '19 at 07:45
That reduced the size to basicly nothing, but what caused it and how to prevent that? — Jaster, Jul 24 '19 at 07:49

AlexK · Accepted Answer · 2019-07-24T12:22:52.257

0

Do you have clustered index on table? If not, it's heap table. When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database. MSDN t-sql DELETE

To reduce size try this one:

ALTER TABLE MyTable REBUILD;
ALTER INDEX ALL ON MyTable REBUILD WITH (FILLFACTOR = 100);

edited Jul 24 '19 at 12:22

answered Jul 24 '19 at 08:06

AlexK

9,447
2
23
32

1

An excellent article on this topic: [DELETE Operation in SQL Server HEAPs](https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/) – Denis Rubashkin Jul 24 '19 at 08:32
would ALTER INDEX ALL ON MyTable REBUILD include the rebuild on the table itself? – Jaster Jul 24 '19 at 11:12
@Jaster You are right, you need rebuild table before rebuild index. Answer updated – AlexK Jul 24 '19 at 12:24

How to determine why a Table is using so much space?

1 Answers1