Date parsed: 18/10/2007 09:22:46
Date: Thu, 18 Oct 2007 16:22:46 -0700

What is the AutoGrowth set to?

How much is allocated, but unused? (DBCC SHOWFILESTATS WITH NO_INFOMSGS)

"milkman" <milkman@discussions.microsoft.com> wrote in message
news:F74627DA-9A5B-4E29-94A8-B6DEE3203B82@microsoft.com...
> We had a problem with a DB where the rate of growth for the datafile
> suddenly
> skyrocketed.
>
> It used to plug along around 50-60GB, and suddenly, within a period of 4-5
> days, it reached 135 B. The log file did need to be truncated, and is now
> done so via a scheduled task, but the data file continued to grow too
> fast.
> A backup was restored a couple of times, but each time, within a day or
> two,
> the datafile would go back to >135GB, and on a couple of occasions, before
> restoring the DB, would reach 200GB.
>
> Nothing seems to have changed with regard to table size between the 50 GB
> DB
> and the 200GB DB; all tables have roughly the same number of records.
>
> Anyway, after some patches were done on the server, the outrageous growth
> stopped (still don't know if there was something that was fixed via a
> patch
> or if it was just coincidence. Database now sits at about 120GB (problem
> started in August, and seems to have stopped, sometime around the
> beginning
> of October), but that is still twice as large as it should be.
>
> Thoughts? Was there a problem with a bad patch in August-ish, that was
> addressed with a recent patch, or is that no likely?

Date parsed: 22/10/2007 00:12:25
Date: Mon, 22 Oct 2007 07:12:25 -0700

> TotalExtents: 1948796
> UsedExtents: 1742974

Well, that blows one theory out of the water. I wondered if something was
expanding the db and then freeing the space.

> Autogrowth is checked, with growth set to 10%

2nd theory blown away. Thought someone might have set the AutoGrow to 80 GB.

> As for the logs, I don't know what they contain yet, but I know that the
> product does a lot of inserts (which may be obvious).

I didn't mean the transaction logs, I ment the SQL Server logs that you look
at via Enterprise Manager (2000), or Managment Studio (2005)

Just curious, but have indexes been added? They use space too, sometimes
more than the data itself.


Sorry I'm not more help,
Jay

>
> Thanks for the reply.
>
> "Jay" wrote:
>
>> What is the AutoGrowth set to?
>>
>> How much is allocated, but unused? (DBCC SHOWFILESTATS WITH NO_INFOMSGS)
>>
>> "milkman" <milkman@discussions.microsoft.com> wrote in message
>> news:F74627DA-9A5B-4E29-94A8-B6DEE3203B82@microsoft.com...
>> > We had a problem with a DB where the rate of growth for the datafile
>> > suddenly
>> > skyrocketed.
>> >
>> > It used to plug along around 50-60GB, and suddenly, within a period of
>> > 4-5
>> > days, it reached 135 B. The log file did need to be truncated, and is
>> > now
>> > done so via a scheduled task, but the data file continued to grow too
>> > fast.
>> > A backup was restored a couple of times, but each time, within a day or
>> > two,
>> > the datafile would go back to >135GB, and on a couple of occasions,
>> > before
>> > restoring the DB, would reach 200GB.
>> >
>> > Nothing seems to have changed with regard to table size between the 50
>> > GB
>> > DB
>> > and the 200GB DB; all tables have roughly the same number of records.
>> >
>> > Anyway, after some patches were done on the server, the outrageous
>> > growth
>> > stopped (still don't know if there was something that was fixed via a
>> > patch
>> > or if it was just coincidence. Database now sits at about 120GB
>> > (problem
>> > started in August, and seems to have stopped, sometime around the
>> > beginning
>> > of October), but that is still twice as large as it should be.
>> >
>> > Thoughts? Was there a problem with a bad patch in August-ish, that was
>> > addressed with a recent patch, or is that no likely?
>>
>>
>>