Column Data size issue

Question

Hi,
I have an "ID" column in the tblAudit table with datatype NUMERIC(18,0).

The table has 45 million records, and the ID column is the clustered primary key.

Generally, this column should occupy less than 1 GB of storage, but on my server, it is occupying 5755 GB for this single column.
Question :

1. Why it was occipied more storage than expected .
2. How to fix it now .

Answer 1

Hi Rama Krishna,

Reason for Excessive Storage

NUMERIC(18,0) only requires 9 bytes per row. For 45 million rows, it should occupy <1 GB.

If it’s showing 5,755 GB, likely causes include:

1. Severe fragmentation or page bloat (especially since it’s a clustered PK).
2. Ghost records / versioning if snapshot isolation is enabled.
3. Database corruption or incorrect storage reporting.

---

Steps to Fix

1. Check fragmentation & table size:

EXEC sp_spaceused 'tblAudit';

2. Rebuild the clustered index:

ALTER INDEX PK_tblAudit_ID ON tblAudit REBUILD;

Consider setting an appropriate FILLFACTOR if there are many inserts.

3. Verify database integrity:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

4. Review the data type:

If ID fits within BIGINT range, consider changing from NUMERIC(18,0) → BIGINT to reduce storage per row.

Answer 2

Because it’s a clustered index, it contains all the table data.