But thats what happening with my tables.
People using my application have (after some months of heavy use of the application) about 80% of fragmented unused space for 20% of data.
And they can't in any way recover that space.
I have lots of users with say, a file of 20 Gb where the data and indexes ocupies 2/3 Gb.

Now I have solved the problem. I analysed the case and decided that it was not positive, in my case, to have a clustered index in each table (The application has some 4k tables). So I created a maintenance routine that creates and drops a clustered index in each table. After executing this, the Reserved (but unused space due to fragmentation) space becomes not alocated space. and becomes available for shrinkdatabase to reclaim.

I also found a lot of people having this same exact problem (and same solution creating clustered indexes) in several newgroups around the world.

I need not say what the users that use my application under MSDE (or SQL Server 2000 Desktop Edition as it should be called now), felt about all this.

Anyway now I now how to overcome this.

Thanks a lot.


>I would not expect to see a lot of fragmentation in a table that doesn't have a clustered index. SQL Server keeps track of the available space on the pages and puts the data on any page where it'll fit.
>
>-Mike
>
>>I found the problem.
>>My tables didn't have any clustered index. I only have non-clustered indexes.
>>So, I wasn't able to defragment the data. SQL Server only permits to defrag indexes.
>>
>>What I did was create a clustered index on each table (this automatically defrags the table data as it reorders the data itself). The I can drop this index and all the space that was ocupied by data fragmentation (that was the so called «unused space»), turned into free space, available to dbcc shrinkfile to reclaim.
>>
>>Thank you anyway.
>>
>>
>>
>>>DBCC SHRINKDATABASE() and DBCC SHRINKFILE() are the correct tools to use. Are you getting somekind of message?
>>>
>>>-Mike
>>>
>>>>I have several databases that have a lot of «Unused Space».
>>>>I can't figure out why nor how to free that space.
>>>>Here is the situation:
>>>>
>>>>I have several tables in the database that report the following kind of values from sp_spaceused:
>>>>
>>>>Reserved: 300000 KB
>>>>Data: 38000 KB
>>>>index_size: 2500 KB
>>>>Unused: 258000 KB
>>>>
>>>>But I can't in any way recover this space. Not using dbcc shrinkdatabase nor using dbcc shrinkfile.
>>>>
>>>>The only way I can free the space is to bulk copy the data out of each table and then back in.
>>>>If in the table where I issued the sp_spaceused before (the example above) i do:
>>>>
>>>>select * into newtable from oldtable
>>>>drop oldtable
>>>>
>>>>and recreate the primary keys, indexes, etc. on the new table, when i do a sp_spaceused on this new table i get something like:
>>>>
>>>>Reserved: 21000 KB
>>>>Data: 20000 KB
>>>>index_size: 500 KB
>>>>Unused: 80 KB
>>>>
>>>>
>>>>Can anyone help me please ? How do I recover the space without bulk copying every table out and in ?