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
>

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 an
d
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 o
n
> 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 recover
y
> 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
>

recovering logins

what is the best way to recover logins.
Let's say I need to rebuild a server or if my server crashes and I need to
reinstall sql server. What can I do so that I have a file or something that
saved my logins so that I can get them back on the server?
I'm used to sybase where you can just bcp the syslogins table.
Search KB for sp_help_revlogins. It will generate a script for you where the script includes
sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
original SID and passwords.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> what is the best way to recover logins.
> Let's say I need to rebuild a server or if my server crashes and I need to
> reinstall sql server. What can I do so that I have a file or something that
> saved my logins so that I can get them back on the server?
> I'm used to sybase where you can just bcp the syslogins table.
|||sorry but what is KB?
"Tibor Karaszi" wrote:

> Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
> original SID and passwords.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
>
>
|||I'm sorry I wasn't thinking - you mean knowledge base articles right?
Ok will do.
Are there any other ways or is this the only way?
"Cinderella" wrote:
[vbcol=seagreen]
> sorry but what is KB?
> "Tibor Karaszi" wrote:
|||> Are there any other ways or is this the only way?
Why do you want other ways to do this? This is the supported way, and it work just as fine as other
potential ways. The thing is that if you start doing some export of sysxlogins or similar, you are
into unsupported territory, without any benefits...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:AA91D7F2-B3E8-4D5F-A690-B037DAD935B5@.microsoft.com...[vbcol=seagreen]
> I'm sorry I wasn't thinking - you mean knowledge base articles right?
> Ok will do.
> Are there any other ways or is this the only way?
> "Cinderella" wrote:
the[vbcol=seagreen]

recovering logins

what is the best way to recover logins.
Let's say I need to rebuild a server or if my server crashes and I need to
reinstall sql server. What can I do so that I have a file or something that
saved my logins so that I can get them back on the server?
I'm used to sybase where you can just bcp the syslogins table.Search KB for sp_help_revlogins. It will generate a script for you where the script includes
sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
original SID and passwords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> what is the best way to recover logins.
> Let's say I need to rebuild a server or if my server crashes and I need to
> reinstall sql server. What can I do so that I have a file or something that
> saved my logins so that I can get them back on the server?
> I'm used to sybase where you can just bcp the syslogins table.|||sorry but what is KB?
"Tibor Karaszi" wrote:
> Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
> original SID and passwords.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > what is the best way to recover logins.
> > Let's say I need to rebuild a server or if my server crashes and I need to
> > reinstall sql server. What can I do so that I have a file or something that
> > saved my logins so that I can get them back on the server?
> >
> > I'm used to sybase where you can just bcp the syslogins table.
>
>|||I'm sorry I wasn't thinking - you mean knowledge base articles right?
Ok will do.
Are there any other ways or is this the only way?
"Cinderella" wrote:
> sorry but what is KB?
> "Tibor Karaszi" wrote:
> > Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> > sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
> > original SID and passwords.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> > news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > > what is the best way to recover logins.
> > > Let's say I need to rebuild a server or if my server crashes and I need to
> > > reinstall sql server. What can I do so that I have a file or something that
> > > saved my logins so that I can get them back on the server?
> > >
> > > I'm used to sybase where you can just bcp the syslogins table.
> >
> >
> >|||> Are there any other ways or is this the only way?
Why do you want other ways to do this? This is the supported way, and it work just as fine as other
potential ways. The thing is that if you start doing some export of sysxlogins or similar, you are
into unsupported territory, without any benefits...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:AA91D7F2-B3E8-4D5F-A690-B037DAD935B5@.microsoft.com...
> I'm sorry I wasn't thinking - you mean knowledge base articles right?
> Ok will do.
> Are there any other ways or is this the only way?
> "Cinderella" wrote:
> > sorry but what is KB?
> >
> > "Tibor Karaszi" wrote:
> >
> > > Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> > > sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using
the
> > > original SID and passwords.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> > > news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > > > what is the best way to recover logins.
> > > > Let's say I need to rebuild a server or if my server crashes and I need to
> > > > reinstall sql server. What can I do so that I have a file or something that
> > > > saved my logins so that I can get them back on the server?
> > > >
> > > > I'm used to sybase where you can just bcp the syslogins table.
> > >
> > >
> > >