MSDE and clustered indexes.

We have this weird problem with our customers using MSDE. They keep running
up against the 2 gig size limit and their database stops functioning because
it can't allocate any more space. But if you examine the database, 75% of
the allocated space is unused. It can't be recovered by shrinking the
database. The only way we can reduce the database size is to copy all the
data into a temporary database, delete it from the original and then
re-insert in the original. Yesterday we did this and a customer's database
was reduced to 528 MB from 1.913 GB. We run a weely job that shrinks and
re-indexes everything but it doesn't have any effect on this problem. I
suspect that it has something to do with the fact that there are no
clustered indexes on any of the table so the re-index job is not doing
anything. But maybe my understanding of this is inaccurate. Is this problem
because of the lack of clustered indexes?If there are no clustered indexes, the table is essentially a "heap". Data
will be appended at the end. If the application delete large amounts of dat
a
and re-inserts it, the most likely be large gaps in the middle.
Objects such as triggers can cause "delete/insert" pairs instead of
"in-place" updates, fill-factor can be a potential cause, etc. Clustered
indexes may not help if the natural key is ever-increasing, since holes will
still exist after large deletes. Non-clustered indexes may help
space-reclamation.
Find out what type of activity is occuring and take appropriate action.
"Bob Castleman" wrote:

> We have this weird problem with our customers using MSDE. They keep runnin
g
> up against the 2 gig size limit and their database stops functioning becau
se
> it can't allocate any more space. But if you examine the database, 75% of
> the allocated space is unused. It can't be recovered by shrinking the
> database. The only way we can reduce the database size is to copy all the
> data into a temporary database, delete it from the original and then
> re-insert in the original. Yesterday we did this and a customer's database
> was reduced to 528 MB from 1.913 GB. We run a weely job that shrinks and
> re-indexes everything but it doesn't have any effect on this problem. I
> suspect that it has something to do with the fact that there are no
> clustered indexes on any of the table so the re-index job is not doing
> anything. But maybe my understanding of this is inaccurate. Is this proble
m
> because of the lack of clustered indexes?
>
>|||Any new devolepments with this issue, where the clustered indexes an issue o
r not? Anyone had a case like this?
Thanks.