Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igor
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>
|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there somethin
g
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure
. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifyin
g
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting
and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As
a
result, we have 40-50 other connections waiting, then next connection
using
this procedure has [COMPILE] lock etc. Client is fully qualifying
stored
procedure by database/owner name and it doesn't start with sp_. I know

these are the reasons for [COMPILE] lock being placed. Is there
something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The
stored
procedure is very simple (I know it could be rewritten/optimized but
our
developer wrote it):

CREATE PROCEDURE [dbo].[vsp_mail_select]

@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified
folder
@.unread_count int = null output -- count of unread mail in specified
folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
@.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0

GO

I was monitoring server for a couple of day before and I am not sure
why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>> Hello!
>> I am trying to investigate strange problem with particular stored
>> procedure. It runs OK for several days and suddenly we start getting and
>> lot of locks. The reason being [COMPILE] lock placed on this procedure.
>> As a result, we have 40-50 other connections waiting, then next
>> connection using this procedure has [COMPILE] lock etc. Client is fully
>> qualifying stored procedure by database/owner name and it doesn't start
>> with sp_. I know these are the reasons for [COMPILE] lock being placed.
>> Is there something else that might trigger this lock? When
>> troubleshooting this issue, I noticed there was no plan for this
>> procedure in syscacheobjects. The stored procedure is very simple (I know
>> it could be rewritten/optimized but our developer wrote it):
>>
>> CREATE PROCEDURE [dbo].[vsp_mail_select]
>> @.user_id int,
>> @.folder_id int,
>> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
>> @.start_index int = null, --unused for now, we return everything
>> @.total_count int = null output, -- count of all mail in specified folder
>> @.unread_count int = null output -- count of unread mail in specified
>> folder
>> AS
>> SET NOCOUNT ON
>> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
>> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
>> order by date_sent desc
>> select @.total_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
>> @.is_read=0) or (@.is_read=1))
>> select @.unread_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
>> GO
>> I was monitoring server for a couple of day before and I am not sure why
>> this happens every 3-4 days only!
>> Any help on this matter would be greately appreciated!
>> Thanks,
>> igor
>

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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>