Column Data size issue

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 .
  1. Viorel 127K Reputation points

    How was it concluded?

  2. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

    Hi @RAMA KRISHNA MANDALAPU ,

    @Abdifatah Hassan Roble thanks for the detailed explanation and the step-by-step fix. This directly addresses both the cause and resolution for the excessive storage issue.

    @RAMA KRISHNA MANDALAPU were you able to run the suggested checks (sp_spaceused, DBCC CHECKDB, and index rebuild)? Did that bring the storage size down to expected levels?

    Thanks,
    Akhil.

  3. Akhil Gajavelly 1,830 Reputation points • Microsoft External Staff • Moderator

    Hi @RAMA KRISHNA MANDALAPU ,

    Just checking in again were you able to perform the recommended checks (sp_spaceused, DBCC CHECKDB, and index rebuild) and review the storage usage? Please let us know the current status so we can assist you further if needed.

    Thanks,
    Akhil.

Sign in to comment

Answer recommended by moderator

Abdifatah Hassan Roble 80 Reputation points

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';

  1. Rebuild the clustered index:

ALTER INDEX PK_tblAudit_ID ON tblAudit REBUILD;

Consider setting an appropriate FILLFACTOR if there are many inserts.

  1. Verify database integrity:

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

  1. Review the data type:

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

0 comments No comments
Sign in to comment

1 additional answer

  1. Bruce (SqlWork.com) 84,086 Reputation points

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

    0 comments No comments
    Sign in to comment
Sign in to answer

Your answer