Table Space Query - data and index size

Hi All,

I have a n SQL Server 2000 database which has data imported each night into it.

The import has been failing for the past few nights so I am investigating as to why.

Now I have noticed that when I use the SPROC sp_spaceused to check the tables for size, I have 2 tables that currently contain 0 rows but the space used shows as:

Table 1
Data: 109264 KB
index_size: 11360 KB

Table 2
Data: 8988288 KB
index_size: 28880 KB

Both tables have primary keys and auto increment set and each night these tables are truncated before new data is inserted. But at the moment these tables are empty.

Can anyone advise why there would be so much data still attached to these tables even though there isn't any rows?


Who is Participating?

Improve company productivity with a Business Account.Sign Up

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
is there a clustered index on the table?
if not, that might explain why the "reserved" space for the table is not released...
rito1Author Commented:
The index is clustered.?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is that a question or a affirmation?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

rito1Author Commented:
Hi angelIII,

Sorry just confused still... my tables have a clustered index on the primary key. So I am still looking into as to why the data still appears so big when there is physically no data in it.

Thanks for your help.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Instead of truncating the table, I'd suggest recreating them then inserting...
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
Also please activate instant file initialization.  SQL Server space allocation/deallocation can be quite tricky...instant file initialization makes the process less painful.  eventually think of rebuilding the index (dbcc reindex) at the end of the import...
rito1Author Commented:
Hi Both,

I have just appended True at the end of my sp_spaceused SPROC and it shows the real statistic rather than a cached version... these stats look a lot better. Thanks for you help anyway.

rito1Author Commented:

I just tried to award the points but it seems to have tried to close the question.. is this correct?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.