Auto Shrink: How do I know if it happened?

Friends,
I read in Kalen Delaney's book that the Auto Shrink implementation always
uses spid 6, and that it cheks at 30 minute intervals. But, I'm not sure
how to tell if an Auto Shrink has actually occurred. Would it appear in the
SQL log file or possibly the event log?
We are tracking a mysterious performance problem at a (very remote) customer
site; one of the two CPUs is utilized 100% by SQL Server 2000, but we can't
really tell what's happening, and suspect perhaps an Auto Shrink is in
progress.
Any words wil be greatly appreciated! Thanks in advance for you help.
James Hunter Ross
Senior Software Developer
O'Neil Software, Inc.
james.ross@.oneilsoft.comYou can use SQL Profiler to track autoshrinks. Personally,
I much prefer to see an entry in the SQL errorlog. It
would be much more light weighted, especially if we are
given a knob to turn it off. Unfortunately, I don't think
that's the case.
Linchi
>--Original Message--
>Friends,
>I read in Kalen Delaney's book that the Auto Shrink
implementation always
>uses spid 6, and that it cheks at 30 minute intervals.
But, I'm not sure
>how to tell if an Auto Shrink has actually occurred.
Would it appear in the
>SQL log file or possibly the event log?
>We are tracking a mysterious performance problem at a
(very remote) customer
>site; one of the two CPUs is utilized 100% by SQL Server
2000, but we can't
>really tell what's happening, and suspect perhaps an Auto
Shrink is in
>progress.
>Any words wil be greatly appreciated! Thanks in advance
for you help.
>James Hunter Ross
>Senior Software Developer
>O'Neil Software, Inc.
>james.ross@.oneilsoft.com
>
>.
>|||We really need to know if it HAS happened, after the fact. We didn't have a
trace running at the time, and there is nobody on-site who is especially
qualified to use Profiler.
James|||If you have no trace from the time, the only way is to look at the log. You
can use a tool like Lumigent Log Explorer to examine the log files for the
sytem initiated shrink (SPID <50), in fact having just seen one happen, it
had a spid of 13.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James Hunter Ross" <james.ross@.oneilinc.com> wrote in message
news:uAsuw2T2DHA.1660@.TK2MSFTNGP09.phx.gbl...
> Friends,
> I read in Kalen Delaney's book that the Auto Shrink implementation always
> uses spid 6, and that it cheks at 30 minute intervals. But, I'm not sure
> how to tell if an Auto Shrink has actually occurred. Would it appear in
the
> SQL log file or possibly the event log?
> We are tracking a mysterious performance problem at a (very remote)
customer
> site; one of the two CPUs is utilized 100% by SQL Server 2000, but we
can't
> really tell what's happening, and suspect perhaps an Auto Shrink is in
> progress.
> Any words wil be greatly appreciated! Thanks in advance for you help.
> James Hunter Ross
> Senior Software Developer
> O'Neil Software, Inc.
> james.ross@.oneilsoft.com
>|||Jasper;
What exactly did you see in the log with Log Explorer that
would indicate an autoshrink? Did you attach Log Explorer
to the user database or the master database? Is it
the 'ShrinkD' in the Desc column?
Linchi
>--Original Message--
>If you have no trace from the time, the only way is to
look at the log. You
>can use a tool like Lumigent Log Explorer to examine the
log files for the
>sytem initiated shrink (SPID <50), in fact having just
seen one happen, it
>had a spid of 13.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"James Hunter Ross" <james.ross@.oneilinc.com> wrote in
message
>news:uAsuw2T2DHA.1660@.TK2MSFTNGP09.phx.gbl...
>> Friends,
>> I read in Kalen Delaney's book that the Auto Shrink
implementation always
>> uses spid 6, and that it cheks at 30 minute intervals.
But, I'm not sure
>> how to tell if an Auto Shrink has actually occurred.
Would it appear in
>the
>> SQL log file or possibly the event log?
>> We are tracking a mysterious performance problem at a
(very remote)
>customer
>> site; one of the two CPUs is utilized 100% by SQL
Server 2000, but we
>can't
>> really tell what's happening, and suspect perhaps an
Auto Shrink is in
>> progress.
>> Any words wil be greatly appreciated! Thanks in
advance for you help.
>> James Hunter Ross
>> Senior Software Developer
>> O'Neil Software, Inc.
>> james.ross@.oneilsoft.com
>>
>
>.
>|||Not much :-)
Just a Transaction that was labelled ShrinkFile in the Desc column with a
spid of 13 and no uid (a user seems to have a UID and spid >50). I set
Northwind to autoshrink and full recovery,did a full + log backup, created a
lot of rows in a table to expand the db then truncated the table and did
another log backup. I then just attached to the log and set the realtime
monitor running and left it for about 40 minutes. Some time later I looked
back and saw the shrink (nothing else is running on this instance not even
SQLAgent) and confirmed the database had shrunk. Unfortunately I have
deleted the log bakups now and its not in the log anymore so I am going from
memory a bit.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Linchi Shea" <inchi_shea@.NOSPAMml.com> wrote in message
news:02c301c3d95b$37da4a90$a601280a@.phx.gbl...
> Jasper;
> What exactly did you see in the log with Log Explorer that
> would indicate an autoshrink? Did you attach Log Explorer
> to the user database or the master database? Is it
> the 'ShrinkD' in the Desc column?
> Linchi
> >--Original Message--
> >If you have no trace from the time, the only way is to
> look at the log. You
> >can use a tool like Lumigent Log Explorer to examine the
> log files for the
> >sytem initiated shrink (SPID <50), in fact having just
> seen one happen, it
> >had a spid of 13.
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"James Hunter Ross" <james.ross@.oneilinc.com> wrote in
> message
> >news:uAsuw2T2DHA.1660@.TK2MSFTNGP09.phx.gbl...
> >> Friends,
> >>
> >> I read in Kalen Delaney's book that the Auto Shrink
> implementation always
> >> uses spid 6, and that it cheks at 30 minute intervals.
> But, I'm not sure
> >> how to tell if an Auto Shrink has actually occurred.
> Would it appear in
> >the
> >> SQL log file or possibly the event log?
> >>
> >> We are tracking a mysterious performance problem at a
> (very remote)
> >customer
> >> site; one of the two CPUs is utilized 100% by SQL
> Server 2000, but we
> >can't
> >> really tell what's happening, and suspect perhaps an
> Auto Shrink is in
> >> progress.
> >>
> >> Any words wil be greatly appreciated! Thanks in
> advance for you help.
> >>
> >> James Hunter Ross
> >> Senior Software Developer
> >> O'Neil Software, Inc.
> >> james.ross@.oneilsoft.com
> >>
> >>
> >
> >
> >.
> >