Reply...

Steve,
Thanks for your feedback! I will implement the code into my normal
maintenance routine as I definitely find it very useful!
I still have a problem though of shrinking the physical size of the log
or changing my maintenance plan so that the log file does not grow so
quickly. I noticed my weekend maintenance plan of REINDEX, etc makes
the log grow very large, very quickly. I desperately need a way to
control the log size when the maintenance is run on the weekend as I
need to maintain over 130 databases.
For example (values are rough estimates), on Friday I may have one log
file that throughout the weekend has maintained it's size of 5MB (mdf
size of 25MB). After running the REINDEX, etc. on a Saturday evening
(between full backups - one on Friday night/another on Sat night), the
log size is now 30MB (mdf file is still 25MB). Throughtout the week,
the trans log maintains it's physical log size of 30MB. The next
weekend, DBREINDEX, etc. kicks off again - now the log file has grown to
55MB. You can see where I'm going here I'm sure by now. I need a way
to keep this log file under control so that every week when I run db
maintenance, it does not keep growing out of control.
Is it as easy as just implementing the code that Steve provided on a
weekend basis and after the REINDEX has run, it will maintain that log
size? That is:
log size before db maintenance: 5MB
Log size after db maintenance (wk1): 30 MB
Run Steve's code
Log size after db maintenance (wk2, etc): 30 MB
Thanks again for everyone's input and if I am missing something obvious,
thanks for your patience!
Rich
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!From your description, it seems that you never empty the log. Emptying the
log is not the same as shrinking the file size of the tlog files. Either do
regular log backups or set the database in simple recovery mode. You will
then see that the log is emptied regularly and each defrag will need some 25
MB in the log, but as the log is emptied regularly, it will never grow
beyond approx 25-30 MB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rich S" <myscullyfamily@.yahoo.com> wrote in message
news:OB%230byECEHA.140@.TK2MSFTNGP09.phx.gbl...
>
> Steve,
> Thanks for your feedback! I will implement the code into my normal
> maintenance routine as I definitely find it very useful!
> I still have a problem though of shrinking the physical size of the log
> or changing my maintenance plan so that the log file does not grow so
> quickly. I noticed my weekend maintenance plan of REINDEX, etc makes
> the log grow very large, very quickly. I desperately need a way to
> control the log size when the maintenance is run on the weekend as I
> need to maintain over 130 databases.
> For example (values are rough estimates), on Friday I may have one log
> file that throughout the weekend has maintained it's size of 5MB (mdf
> size of 25MB). After running the REINDEX, etc. on a Saturday evening
> (between full backups - one on Friday night/another on Sat night), the
> log size is now 30MB (mdf file is still 25MB). Throughtout the week,
> the trans log maintains it's physical log size of 30MB. The next
> weekend, DBREINDEX, etc. kicks off again - now the log file has grown to
> 55MB. You can see where I'm going here I'm sure by now. I need a way
> to keep this log file under control so that every week when I run db
> maintenance, it does not keep growing out of control.
> Is it as easy as just implementing the code that Steve provided on a
> weekend basis and after the REINDEX has run, it will maintain that log
> size? That is:
> log size before db maintenance: 5MB
> Log size after db maintenance (wk1): 30 MB
> Run Steve's code
> Log size after db maintenance (wk2, etc): 30 MB
> Thanks again for everyone's input and if I am missing something obvious,
> thanks for your patience!
> Rich
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!