Recovery Model

I've created an index defrag script, based on a set of criteria it will
choose whether to do a reorganise or rebuild (online or offline) for a
selected index.. ..I've set that when it does a rebuild to set the recovery
model to bulk logged.. ..I set the recovery model just before the rebuild and
revert just after, I've since been thinking if it needs to 100 rebuilds it
will switch the recovery model 100 times for the duration of each rebuild..
...would this be bad practice?
Ben
What is your current recover model? Is it FULL? Do you perform BACKUP LOG
operation?
Have you noticed a big difference in terms of performance if you run it on
FULL or BULK LOGGED recover model?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the
> recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild
> and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each
> rebuild..
> ..would this be bad practice?
|||Current recovery model is Full, yes we do back up the logs.. ..this is to be
used on multiple servers with multiple databases so the frequency of log
backups can very greatly, the indexes this would be rebuilding are
potentially very large so I'm trying to avoid hammering the log in between
the log backups...
"Uri Dimant" wrote:

> Ben
> What is your current recover model? Is it FULL? Do you perform BACKUP LOG
> operation?
> Have you noticed a big difference in terms of performance if you run it on
> FULL or BULK LOGGED recover model?
>
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
>
>
|||Ben
Well , if you have enuogh free space , I don't think you need to switch
between FULL and BULL LOGGED as well as i have not seem such performance
issues.
However , can you rebuild your indexes at night for example where workload
is low.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5AF71BFA-7B0A-4692-B227-AB66F44442AE@.microsoft.com...[vbcol=seagreen]
> Current recovery model is Full, yes we do back up the logs.. ..this is to
> be
> used on multiple servers with multiple databases so the frequency of log
> backups can very greatly, the indexes this would be rebuilding are
> potentially very large so I'm trying to avoid hammering the log in between
> the log backups...
> "Uri Dimant" wrote:
|||Ben UK wrote:
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each rebuild..
> ..would this be bad practice?
I wouldn't say it's "bad practice". I would, however, be concerned
about the possibility of some bulk operation being performed while you
have the recovery model switched. In other words, if you're automating
this switch between Full and Bulk-Logged, how will you control or
prevent some other process from doing something non-logged? A scheduled
maintenance window seems like a much "safer" way of managing reindexes.
Just my 2-cents.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||That makes sense, thanks for the input Tracy
"Tracy McKibben" wrote:

> Ben UK wrote:
> I wouldn't say it's "bad practice". I would, however, be concerned
> about the possibility of some bulk operation being performed while you
> have the recovery model switched. In other words, if you're automating
> this switch between Full and Bulk-Logged, how will you control or
> prevent some other process from doing something non-logged? A scheduled
> maintenance window seems like a much "safer" way of managing reindexes.
> Just my 2-cents.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>