Excessive data file growth.

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?Sorry, need to add that the objective of this post is two-fold:
1. Figure out what happened to cause the runaway data file growth.
2. Figure out how to get the DB back down to size.
"milkman" wrote:
> 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?|||Arghh... Sorry. MSSQL 2000, SP4. The database is one of many instances on
the server.
"milkman" wrote:
> Sorry, need to add that the objective of this post is two-fold:
> 1. Figure out what happened to cause the runaway data file growth.
> 2. Figure out how to get the DB back down to size.
> "milkman" wrote:
> > 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?|||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?|||Silly me. When the DB grows, what to the logs have to say?
"Jay" <spam@.nospam.org> wrote in message
news:ONtbJ3dEIHA.280@.TK2MSFTNGP03.phx.gbl...
> 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?
>|||TotalExtents: 1948796
UsedExtents: 1742974
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). Autogrowth is checked,
with growth set to 10%
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?
>
>|||> 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?
>>|||Jay,
Thanks for the attempt. I have asked for the SQL logs.
One other piece of info. We ran a query a few weeks ago (so this info may
be old, but I still think it applies.) that uses a stored proc named
sp_spaceused, and the results for the biggest table are as follows:
Table_Rows 37,616,120
Reserved (KB) 54,835,760
Data (KB) 8,553,000
Index_Size (KB) 2,999,192
Unused (KB) 43,283,568
Used_MB 53,550
Used_GB 52.2955
Average_Bytes_Per_Row 1492.75944
Average_Data_Bytes_Per_Row 232.83295
Average_Index_Bytes_Per_Row 81.64512
Average_Unused_Bytes_Per_Row 1178.28138
Don't know if this gives you anything, but it's what I have.
Thanks,
Tyler
"Jay" wrote:
> > 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?
> >>
> >>
> >>
>
>|||I don't use sp_spaceused, I use DBCC SHOWFILESTATS, which returns a limited,
but more accurate dataset.
Still, for this one table (if I'm reading it right), says that 43 of the 54
GB is unused (serious rounding error on my numbers).
That means that a ton of rows were inserted and then removed.
I am beginning to suspect a huge transaction that was rolled back, or the
rows were otherwise deleted. I don't think that rollbacks are logged by SQL
Server though.
Insert a row, fill up the .mdf, auto expansion, delete/rollback,
available space stays.
Whatever the situtation is, it sounds like it isn't SQL Server, but
something in your enviornment.
Jay
"milkman" <milkman@.discussions.microsoft.com> wrote in message
news:2B887237-0523-47CF-BF1E-7D5CAC9D9231@.microsoft.com...
> Jay,
> Thanks for the attempt. I have asked for the SQL logs.
> One other piece of info. We ran a query a few weeks ago (so this info may
> be old, but I still think it applies.) that uses a stored proc named
> sp_spaceused, and the results for the biggest table are as follows:
> Table_Rows 37,616,120
> Reserved (KB) 54,835,760
> Data (KB) 8,553,000
> Index_Size (KB) 2,999,192
> Unused (KB) 43,283,568
> Used_MB 53,550
> Used_GB 52.2955
> Average_Bytes_Per_Row 1492.75944
> Average_Data_Bytes_Per_Row 232.83295
> Average_Index_Bytes_Per_Row 81.64512
> Average_Unused_Bytes_Per_Row 1178.28138
> Don't know if this gives you anything, but it's what I have.
> Thanks,
> Tyler
> "Jay" wrote:
>> > 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?
>> >>
>> >>
>> >>
>>|||Interesting theory. Well, no matter what happened, they are going to be
monitoring it more closely to so that we can troubleshoot it while it's
happening.
To get us back to a smaller size DB, I think we're going to try creating a
new table and then inserting the records from the old one. We tried that on
another DB when a similar thing happened 6 months or so ago, and it took one
of the tables from 49GB down to 2.5GB.
Thanks for your help on this, and I'll append with results, especially if I
come across an explanation.
"Jay" wrote:
> I don't use sp_spaceused, I use DBCC SHOWFILESTATS, which returns a limited,
> but more accurate dataset.
> Still, for this one table (if I'm reading it right), says that 43 of the 54
> GB is unused (serious rounding error on my numbers).
> That means that a ton of rows were inserted and then removed.
> I am beginning to suspect a huge transaction that was rolled back, or the
> rows were otherwise deleted. I don't think that rollbacks are logged by SQL
> Server though.
> Insert a row, fill up the .mdf, auto expansion, delete/rollback,
> available space stays.
> Whatever the situtation is, it sounds like it isn't SQL Server, but
> something in your enviornment.
> Jay
> "milkman" <milkman@.discussions.microsoft.com> wrote in message
> news:2B887237-0523-47CF-BF1E-7D5CAC9D9231@.microsoft.com...
> > Jay,
> >
> > Thanks for the attempt. I have asked for the SQL logs.
> >
> > One other piece of info. We ran a query a few weeks ago (so this info may
> > be old, but I still think it applies.) that uses a stored proc named
> > sp_spaceused, and the results for the biggest table are as follows:
> >
> > Table_Rows 37,616,120
> > Reserved (KB) 54,835,760
> > Data (KB) 8,553,000
> > Index_Size (KB) 2,999,192
> > Unused (KB) 43,283,568
> > Used_MB 53,550
> > Used_GB 52.2955
> > Average_Bytes_Per_Row 1492.75944
> > Average_Data_Bytes_Per_Row 232.83295
> > Average_Index_Bytes_Per_Row 81.64512
> > Average_Unused_Bytes_Per_Row 1178.28138
> >
> > Don't know if this gives you anything, but it's what I have.
> >
> > Thanks,
> >
> > Tyler
> >
> > "Jay" wrote:
> >
> >> > 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||I think you're about to do a lot of work that won't solve your problem.
I would seriously suggest that you start to monitor both the file size and
the space used of .mdf, .ndf & .ldf files so you can say when the growth is
happening (and the freeing of the space). You already have sp_spaceused,
make a small proc that pulls the numbers every 15, 30, or 60 minutes and
stores them in a table. In the grand scheme of things you won't use much
drive space and the CPU ticks are minimal.
Also, you WILL bust whatever is forcing the AutoGrow and freeing the space.
"milkman" <milkman@.discussions.microsoft.com> wrote in message
news:16D8DFAB-CCF5-45EA-B0E4-696F10D56FB8@.microsoft.com...
> Interesting theory. Well, no matter what happened, they are going to be
> monitoring it more closely to so that we can troubleshoot it while it's
> happening.
> To get us back to a smaller size DB, I think we're going to try creating a
> new table and then inserting the records from the old one. We tried that
> on
> another DB when a similar thing happened 6 months or so ago, and it took
> one
> of the tables from 49GB down to 2.5GB.
> Thanks for your help on this, and I'll append with results, especially if
> I
> come across an explanation.
> "Jay" wrote:
>> I don't use sp_spaceused, I use DBCC SHOWFILESTATS, which returns a
>> limited,
>> but more accurate dataset.
>> Still, for this one table (if I'm reading it right), says that 43 of the
>> 54
>> GB is unused (serious rounding error on my numbers).
>> That means that a ton of rows were inserted and then removed.
>> I am beginning to suspect a huge transaction that was rolled back, or the
>> rows were otherwise deleted. I don't think that rollbacks are logged by
>> SQL
>> Server though.
>> Insert a row, fill up the .mdf, auto expansion, delete/rollback,
>> available space stays.
>> Whatever the situtation is, it sounds like it isn't SQL Server, but
>> something in your enviornment.
>> Jay
>> "milkman" <milkman@.discussions.microsoft.com> wrote in message
>> news:2B887237-0523-47CF-BF1E-7D5CAC9D9231@.microsoft.com...
>> > Jay,
>> >
>> > Thanks for the attempt. I have asked for the SQL logs.
>> >
>> > One other piece of info. We ran a query a few weeks ago (so this info
>> > may
>> > be old, but I still think it applies.) that uses a stored proc named
>> > sp_spaceused, and the results for the biggest table are as follows:
>> >
>> > Table_Rows 37,616,120
>> > Reserved (KB) 54,835,760
>> > Data (KB) 8,553,000
>> > Index_Size (KB) 2,999,192
>> > Unused (KB) 43,283,568
>> > Used_MB 53,550
>> > Used_GB 52.2955
>> > Average_Bytes_Per_Row 1492.75944
>> > Average_Data_Bytes_Per_Row 232.83295
>> > Average_Index_Bytes_Per_Row 81.64512
>> > Average_Unused_Bytes_Per_Row 1178.28138
>> >
>> > Don't know if this gives you anything, but it's what I have.
>> >
>> > Thanks,
>> >
>> > Tyler
>> >
>> > "Jay" wrote:
>> >
>> >> > 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Jay,
That's a good idea, and something that someone in the team had mentioned
before, but did not go ahead and do it. I'll politely "suggest" that it be
implemented. :-)
Thanks, and I'll keep you posted...
"Jay" wrote:
> I think you're about to do a lot of work that won't solve your problem.
> I would seriously suggest that you start to monitor both the file size and
> the space used of .mdf, .ndf & .ldf files so you can say when the growth is
> happening (and the freeing of the space). You already have sp_spaceused,
> make a small proc that pulls the numbers every 15, 30, or 60 minutes and
> stores them in a table. In the grand scheme of things you won't use much
> drive space and the CPU ticks are minimal.
> Also, you WILL bust whatever is forcing the AutoGrow and freeing the space.
>
> "milkman" <milkman@.discussions.microsoft.com> wrote in message
> news:16D8DFAB-CCF5-45EA-B0E4-696F10D56FB8@.microsoft.com...
> > Interesting theory. Well, no matter what happened, they are going to be
> > monitoring it more closely to so that we can troubleshoot it while it's
> > happening.
> >
> > To get us back to a smaller size DB, I think we're going to try creating a
> > new table and then inserting the records from the old one. We tried that
> > on
> > another DB when a similar thing happened 6 months or so ago, and it took
> > one
> > of the tables from 49GB down to 2.5GB.
> >
> > Thanks for your help on this, and I'll append with results, especially if
> > I
> > come across an explanation.
> >
> > "Jay" wrote:
> >
> >> I don't use sp_spaceused, I use DBCC SHOWFILESTATS, which returns a
> >> limited,
> >> but more accurate dataset.
> >>
> >> Still, for this one table (if I'm reading it right), says that 43 of the
> >> 54
> >> GB is unused (serious rounding error on my numbers).
> >>
> >> That means that a ton of rows were inserted and then removed.
> >>
> >> I am beginning to suspect a huge transaction that was rolled back, or the
> >> rows were otherwise deleted. I don't think that rollbacks are logged by
> >> SQL
> >> Server though.
> >>
> >> Insert a row, fill up the .mdf, auto expansion, delete/rollback,
> >> available space stays.
> >>
> >> Whatever the situtation is, it sounds like it isn't SQL Server, but
> >> something in your enviornment.
> >>
> >> Jay
> >>
> >> "milkman" <milkman@.discussions.microsoft.com> wrote in message
> >> news:2B887237-0523-47CF-BF1E-7D5CAC9D9231@.microsoft.com...
> >> > Jay,
> >> >
> >> > Thanks for the attempt. I have asked for the SQL logs.
> >> >
> >> > One other piece of info. We ran a query a few weeks ago (so this info
> >> > may
> >> > be old, but I still think it applies.) that uses a stored proc named
> >> > sp_spaceused, and the results for the biggest table are as follows:
> >> >
> >> > Table_Rows 37,616,120
> >> > Reserved (KB) 54,835,760
> >> > Data (KB) 8,553,000
> >> > Index_Size (KB) 2,999,192
> >> > Unused (KB) 43,283,568
> >> > Used_MB 53,550
> >> > Used_GB 52.2955
> >> > Average_Bytes_Per_Row 1492.75944
> >> > Average_Data_Bytes_Per_Row 232.83295
> >> > Average_Index_Bytes_Per_Row 81.64512
> >> > Average_Unused_Bytes_Per_Row 1178.28138
> >> >
> >> > Don't know if this gives you anything, but it's what I have.
> >> >
> >> > Thanks,
> >> >
> >> > Tyler
> >> >
> >> > "Jay" wrote:
> >> >
> >> >> > 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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>