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
quote:
>--Original Message--
>Friends,
>I read in Kalen Delaney's book that the Auto Shrink
implementation always
quote:
>uses spid 6, and that it cheks at 30 minute intervals.
But, I'm not sure
quote:
>how to tell if an Auto Shrink has actually occurred.
Would it appear in the
quote:
>SQL log file or possibly the event log?
>We are tracking a mysterious performance problem at a
(very remote) customer
quote:
>site; one of the two CPUs is utilized 100% by SQL Server
2000, but we can't
quote:
>really tell what's happening, and suspect perhaps an Auto
Shrink is in
quote:
>progress.
>Any words wil be greatly appreciated! Thanks in advance
for you help.
quote:|||We really need to know if it HAS happened, after the fact. We didn't have a
>James Hunter Ross
>Senior Software Developer
>O'Neil Software, Inc.
>james.ross@.oneilsoft.com
>
>.
>
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...
quote:
> 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
quote:
> SQL log file or possibly the event log?
> We are tracking a mysterious performance problem at a (very remote)
customer
quote:
> site; one of the two CPUs is utilized 100% by SQL Server 2000, but we
can't
quote:|||Jasper;
> 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
>
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
quote:
>--Original Message--
>If you have no trace from the time, the only way is to
look at the log. You
quote:
>can use a tool like Lumigent Log Explorer to examine the
log files for the
quote:
>sytem initiated shrink (SPID <50), in fact having just
seen one happen, it
quote:
>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
quote:|||Not much :-)
>news:uAsuw2T2DHA.1660@.TK2MSFTNGP09.phx.gbl...
implementation always[QUOTE]
But, I'm not sure[QUOTE]
Would it appear in[QUOTE]
>the
(very remote)[QUOTE]
>customer
Server 2000, but we[QUOTE]
>can't
Auto Shrink is in[QUOTE]
advance for you help.[QUOTE]
>
>.
>
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...[QUOTE]
> 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
>
> look at the log. You
> log files for the
> seen one happen, it
> message
> implementation always
> But, I'm not sure
> Would it appear in
> (very remote)
> Server 2000, but we
> Auto Shrink is in
> advance for you help.
No comments:
Post a Comment