Auto Grow/Shrink

I've got a half terabyte 2000 server with 100 + databases on it. The larger
databases I've taken off of auto shrink. But I am wondering if the 80-90
smaller databases are causing some performance issues with auto shrinks
(which I left on just for ease/convenience).
To check the server out, I turned Profiler on and captured various Auth
Shrink/Grow events. I ran this overnight and into the production hours toda
y
and I got very few of these and so I'm wondering if I did it right.
I simply selected from my trace where event class in 92-95. Is that the
right way to do it' (I want to make sure that I'm not missing these.)CLM,
Take a look at:
http://www.aspfaq.com/show.asp?id=2471
and
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A69B6508-CE0A-4AA7-8575-13D593CE1353@.microsoft.com...
> I've got a half terabyte 2000 server with 100 + databases on it. The
> larger
> databases I've taken off of auto shrink. But I am wondering if the 80-90
> smaller databases are causing some performance issues with auto shrinks
> (which I left on just for ease/convenience).
> To check the server out, I turned Profiler on and captured various Auth
> Shrink/Grow events. I ran this overnight and into the production hours
> today
> and I got very few of these and so I'm wondering if I did it right.
> I simply selected from my trace where event class in 92-95. Is that the
> right way to do it' (I want to make sure that I'm not missing these.)|||Thx, but these I know. I have an unusual configuration that imo requires
autoshrink (or a lot more disk).
Did I do the querying correctly?
"Jerry Spivey" wrote:

> CLM,
> Take a look at:
> http://www.aspfaq.com/show.asp?id=2471
> and
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> HTH
> Jerry
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:A69B6508-CE0A-4AA7-8575-13D593CE1353@.microsoft.com...
>
>|||CLM,
Yes the 'Data File Auto Grow', 'Data File Auto Shrink', 'Log File Auto
Grow', 'Log File Auto Shrink' event classes are the correct ones to use.
However, I really wouldn't recommend having 80-90 databases auto growing and
auto shrinking as this is likely to cause fragmentation and reduce
performance.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:8DD5AE5E-2B7D-4891-A21A-31276BBF47F5@.microsoft.com...[vbcol=seagreen]
> Thx, but these I know. I have an unusual configuration that imo requires
> autoshrink (or a lot more disk).
> Did I do the querying correctly?
> "Jerry Spivey" wrote:
>|||I understand. But the problem I have - and I inherited this by the way - is
that most of those databases have one table with a clustered and a
nonclustered index. When I reindex them, the database expands to a little
over twice its size. And I just don't have the space for that to stay that
way.
I've got index defrags on the larger databases, but I can't have that on
every database.
If there's an easier/better way, I'm open to it of course...
"Jerry Spivey" wrote:

> CLM,
> Yes the 'Data File Auto Grow', 'Data File Auto Shrink', 'Log File Auto
> Grow', 'Log File Auto Shrink' event classes are the correct ones to use.
> However, I really wouldn't recommend having 80-90 databases auto growing a
nd
> auto shrinking as this is likely to cause fragmentation and reduce
> performance.
> HTH
> Jerry
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:8DD5AE5E-2B7D-4891-A21A-31276BBF47F5@.microsoft.com...
>
>|||Hmmm...
1. Extend your array(s) to fit the storage needs of the business
2. Use fewer databases - less space and will also be easier to manage
3. Try using index defrag on the smaller databases
4. Move some of these databases to another server with more space
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:DB60B945-AB19-488C-8169-ADD24B00FB41@.microsoft.com...[vbcol=seagreen]
>I understand. But the problem I have - and I inherited this by the way -
>is
> that most of those databases have one table with a clustered and a
> nonclustered index. When I reindex them, the database expands to a little
> over twice its size. And I just don't have the space for that to stay
> that
> way.
> I've got index defrags on the larger databases, but I can't have that on
> every database.
> If there's an easier/better way, I'm open to it of course...
>
> "Jerry Spivey" wrote:
>|||Why do you reindex? The following shrink will largely undo the defragmentati
on that the reindex
performed...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:DB60B945-AB19-488C-8169-ADD24B00FB41@.microsoft.com...[vbcol=seagreen]
>I understand. But the problem I have - and I inherited this by the way - i
s
> that most of those databases have one table with a clustered and a
> nonclustered index. When I reindex them, the database expands to a little
> over twice its size. And I just don't have the space for that to stay tha
t
> way.
> I've got index defrags on the larger databases, but I can't have that on
> every database.
> If there's an easier/better way, I'm open to it of course...
>
> "Jerry Spivey" wrote:
>|||Oh, I feel like an idiot! I was not thinking about it clearly. I tested
what you said and of course it came to exactly what you said - the scan
density went back to virutally what it was before the reindex!! I appreciat
e
so much your taking time to answer stupid questions...
"Tibor Karaszi" wrote:

> Why do you reindex? The following shrink will largely undo the defragmenta
tion that the reindex
> performed...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:DB60B945-AB19-488C-8169-ADD24B00FB41@.microsoft.com...
>
>|||You're not the only one who shrink without realizing the consequences. I'm g
lad you figured it out.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:FF383A38-6E62-4B98-A621-3333A5117696@.microsoft.com...[vbcol=seagreen]
> Oh, I feel like an idiot! I was not thinking about it clearly. I tested
> what you said and of course it came to exactly what you said - the scan
> density went back to virutally what it was before the reindex!! I appreci
ate
> so much your taking time to answer stupid questions...
> "Tibor Karaszi" wrote:
>