Is there any way to tell when a database file grew

We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5[vbcol=seagreen]
>|||Growing in advance is much better. Grow doesn't block operations for which s
pace is available
(operations that uses current space (on same page, extent etc)) or space fre
e in the db file (as it
is when you grow in advance). Grow does block operations that requires new s
pace which isn't
available (which is the case when all extents are used and you do, say, an i
nsert on a table for
which all current extents are used and all pages on those extents are full,
and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...[vbco
l=seagreen]
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>[/vbcol]