Reduce reserved space for table?

Question

Reduce reserved space for table?

Mr Mostard

Old Hand

Points: 314
More actions
August 9, 2005 at 6:34 am

#165572
Greetings,
I have a table here with 10M rows. After having troubles with DBCC DBREINDEX
telling me there is not enough available on the filegroup (while there
is about 2GB free), I investigated with sp_spaceused, which gives the
following result (dots inserted for readability):
```
name        rows        reserved       data         index_size   unused             
----------- ----------- -------------- ------------ ------------ --------------
Provision   10.841.146  2.473.040 KB   790.208 KB   321.320 KB   1.36.1512 KB
```
Thus, the table reserved about more than double the space it really
needs. Is there something I can do about that?
Thanks,

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

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

M.S. Reddy SSC Veteran Points: 282 More actions · Answer 1

If you are getting space related issues, I assume that you are trying to rebuild all the indexes on the table (or Database) at once. Try these steps

Drop the Non Clustered Indexes on this table

Reindex the Clustered index on this Table (if any). This will reorganize the table and release the space as well.

If there is no Clustered index, then Create a Clustered index and drop it if you do not want it. This reorganizes the table with out affecting the Foreign keys and releases the space.

Create Non Clustered Indexes now.

Hope this helps

M.S. Reddy

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 2

My experience tells me that you always need at least one time the size of the table to be reindex of free space. So if the table is 2 GB (even that only uses 320MB), you may need another 2 GB of free space. This may be causing the error.

Check the fill factor on the indexes and/or the Cluster key. That may be causing the wasted space or maybe the table is too fragmented that you have so much space wasted.

To fix it try:

1. Copy the content of the table to another temp table, truncate the original table, drop all indexes, then insert the records from the temp table again and re-create all indexes again.

2. Or add more space to the offending filegroup.

Hope this helps.

Mr Mostard Old Hand Points: 314 More actions · Answer 3

I already checked fill factors and fragmentation.

Finally I fixed the table by forcing a usage update for the table

( exec sp_spaceused 'provision', 'true' ) and suddenly all the wasted

space that was "reserved" was freed.

I will now include that recalc parameter in my weekly maintenance batches.

Thanks for the replies/ideas!

M.