|
|
| brogers5884 2006-10-24, 6:33 pm |
| When we run the "EXEC SP_SPACEUSED" command it is reporting 22GB of unused
space yet every day that unused space grows by about 1GB. The data size and
index is only roughly 11GB. So my question is why is there so much unused
space and it why is continually growing and what we can we do to prevent it
from continually growing out of control. Thank you.
| |
| Andrew J. Kelly 2006-10-24, 6:33 pm |
| Make sure you run that with the UPDATE_USAGE option but my guess is you have
a lot of fragmentation possibly from Page splits and deletes. Do you run
regular index maintenance?
--
Andrew J. Kelly SQL MVP
"brogers5884" < brogers5884@discussi
ons.microsoft.com> wrote in message
news:5C02A34B-64AF-4684-AC55- F6488503F96C@microso
ft.com...
> When we run the "EXEC SP_SPACEUSED" command it is reporting 22GB of unused
> space yet every day that unused space grows by about 1GB. The data size
> and
> index is only roughly 11GB. So my question is why is there so much unused
> space and it why is continually growing and what we can we do to prevent
> it
> from continually growing out of control. Thank you.
| |
| brogers5884 2006-10-24, 6:33 pm |
| We do use DBCC DBREINDEX alot, however we aren't using clustered indexes so
from my understanding DBREINDEX will not affect DB size if the tables don't
have clustered indexes.
"Andrew J. Kelly" wrote:
> Make sure you run that with the UPDATE_USAGE option but my guess is you have
> a lot of fragmentation possibly from Page splits and deletes. Do you run
> regular index maintenance?
>
> --
> Andrew J. Kelly SQL MVP
>
> "brogers5884" < brogers5884@discussi
ons.microsoft.com> wrote in message
> news:5C02A34B-64AF-4684-AC55- F6488503F96C@microso
ft.com...
>
>
>
| |
| Andrew J. Kelly 2006-10-24, 6:33 pm |
| That is not true. What it means is that when you reindex you don't do
anything to the tables (the actual data) just the non-clustered indexes. But
when you rebuild a non-clustered index it also must be rebuilt in a part of
the db that has free space. Since you have all heaps you may be having
issues where you delete a lot of rows but the new rows can't fit where the
old ones were leaving gaps. Is there a specific reason why you aren't using
clustered indexes?
--
Andrew J. Kelly SQL MVP
"brogers5884" < brogers5884@discussi
ons.microsoft.com> wrote in message
news:6CF9111A-E75C-438D-9F3E- 8BB013BF04F8@microso
ft.com...[color=darkred]
> We do use DBCC DBREINDEX alot, however we aren't using clustered indexes
> so
> from my understanding DBREINDEX will not affect DB size if the tables
> don't
> have clustered indexes.
>
> "Andrew J. Kelly" wrote:
>
| |
| brogers5884 2006-10-24, 6:33 pm |
| The previous DB person had read that clustered indexes can cause alot of file
growth so he changed all of the clustered indexes to non-clustered. I was
reading yesterday that a fair number of people had the same problem that we
do and then changing from non-clustered to clustered fixed their problem. We
don't delete very many rows at all, I'm working purge routines but they
aren't in place and we are seeing file growth of over 1gb per day on some of
the databases. Some of our larger client DB's are getting in the range of
50gb and basically I create a clean database and copy over all of the objects
and data and it shrinks down to roughly 8 to 10gb so I'm trying to find a way
to control that file growth. It will go like mad for a month and then stop
growing like that, and then start growing again down the road. But in any
case it's a real headache.
"Andrew J. Kelly" wrote:
> That is not true. What it means is that when you reindex you don't do
> anything to the tables (the actual data) just the non-clustered indexes. But
> when you rebuild a non-clustered index it also must be rebuilt in a part of
> the db that has free space. Since you have all heaps you may be having
> issues where you delete a lot of rows but the new rows can't fit where the
> old ones were leaving gaps. Is there a specific reason why you aren't using
> clustered indexes?
>
> --
> Andrew J. Kelly SQL MVP
>
> "brogers5884" < brogers5884@discussi
ons.microsoft.com> wrote in message
> news:6CF9111A-E75C-438D-9F3E- 8BB013BF04F8@microso
ft.com...
>
>
>
| |
| Andrew J. Kelly 2006-10-24, 6:33 pm |
| You can control issues related to page splits with clustered indexes by
placing the clustered index on the right column(s) and using an appropriate
fill factor. Moving data into another db just to keep the size down is quite
a drastic and potentially painful measure. I would attempt to determine how
your largest tables are being used and place a clustered index on the
appropriate column on those tables to help control this and stop you from
copying all that data.
--
Andrew J. Kelly SQL MVP
"brogers5884" < brogers5884@discussi
ons.microsoft.com> wrote in message
news:641F4DBB-BBD3-405F-97BA- 3CDE1E48B58B@microso
ft.com...[color=darkred]
> The previous DB person had read that clustered indexes can cause alot of
> file
> growth so he changed all of the clustered indexes to non-clustered. I was
> reading yesterday that a fair number of people had the same problem that
> we
> do and then changing from non-clustered to clustered fixed their problem.
> We
> don't delete very many rows at all, I'm working purge routines but they
> aren't in place and we are seeing file growth of over 1gb per day on some
> of
> the databases. Some of our larger client DB's are getting in the range of
> 50gb and basically I create a clean database and copy over all of the
> objects
> and data and it shrinks down to roughly 8 to 10gb so I'm trying to find a
> way
> to control that file growth. It will go like mad for a month and then
> stop
> growing like that, and then start growing again down the road. But in any
> case it's a real headache.
>
> "Andrew J. Kelly" wrote:
>
|
|
|
|