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.

MSDE - Just Stops!

Hi:
I have a Win2000 server that has been running MSDE for about five months.
Suddenly today it just stopped running.
There were no entries in the event viewer or anything. I stopped and
restarted the service and it still did not respond.
Finally I rebooted the computer and it's working once again. I rebooted the
machine last night so it had only been running about 12 hours and was
functional this morning.
Does anyone have any suggestions on how to determine what happened - and
prevent it from happening in the future?
Thanks for your help!
Fred
Hi,
Verify the SQL Server Logs as well for any errors. It is always recommented
to check the logs atleast once a day.
Check if there is any memory leak 3rd paqrt programs in ur server.
Thanks
Hari
SQL Server MVP
"Fred Nelson" <fred@.smartybird.com> wrote in message
news:umZDRJNUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a Win2000 server that has been running MSDE for about five months.
> Suddenly today it just stopped running.
> There were no entries in the event viewer or anything. I stopped and
> restarted the service and it still did not respond.
> Finally I rebooted the computer and it's working once again. I rebooted
the
> machine last night so it had only been running about 12 hours and was
> functional this morning.
> Does anyone have any suggestions on how to determine what happened - and
> prevent it from happening in the future?
> Thanks for your help!
> Fred
>