Different Table size but data and table structure

Question

Different Table size but data and table structure

tapmrp1

Right there with Babe

Points: 734
More actions
August 26, 2002 at 9:47 am

#64253

I created a same table on two different server with same data. I run sp_spaceused on a table both server and I got following result
it's useing 392 MB for table
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
DUNS_SITE 100000 401288 KB 400000 KB 1264 KB 24 KB
its using only 97.3 MB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
DUNS_SITE 100000 99720 KB 99376 KB 328 KB 16 KB
WHY?????
Thanks
Manesh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Several issues can affect this. One is an index is incorrectly reporting its' size. Delete all the indexes and add back then see if resizes. First however try DBCC UPDATEUSAGE which

quote:
Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

katravax Old Hand Points: 353 More actions · Answer 2

Do the servers have different settings for the default index fill factor? That would do it, if you had a clustered index and the first server had a fixed small fill factor.

To check that setting in SQL Enterprise Manager, right-click the server, and choose "Properties". In the Properties dialog, click the "Database Settings" tab.

To check it from Query Analyzer, run this:

exec sp_configure 'fill factor'

I'm guessing the server that's taking more space has a fixed 25% or lower fill factor.

- Troy King

tapmrp1 Right there with Babe Points: 734 More actions · Answer 3

Thanks Katracvax. I drop the index and recreated.

Now both servers has same table size.