One more question on best-practice for Backup Plan

I am not a SQL Server DBA, but have a temporary need to play that role. I am
new to the Maintenance Plan Wizard, but have thus far implemented the
following:
1. Daily System Full Backup Job (Selected System Databases)
1.1. Check Database Integrity
1.2. Rebuild Index
1.3. Update Statistics
1.4. Clean Up History
1.5. Backup Database (Full)
1.6. Runs daily at 6:15AM
2. Weekly User Full Backup Job (Selected All User Databases)
2.1. Check Database Integrity
2.2. Rebuild Index
3.3. Update Statistics
3.4. Clean Up History
3.5. Backup Database (Full)
3.6. Runs every Saturday at 6:15AM
3. Daily User Differential Backup Job (Selected All User Databases)
3.1. Check Database Integrity
3.2. Backup Database (Differential)
3.3. Runs Sun thru Fri at 6:15AM
4. Hourly User Log Backup Job (Selected All User Databases)
4.1. Backup Database (Transaction Log)
4.1. Runs Hourly Sun thru Sat at 6:00AM
This doesn't seem quite right to me. I am mostly confused by the additional
maintance options in the Wizard (Check Database Integrity, Shrink Database,
Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
Execute SQL Server Agent Job). I am wondering if these should be included in
the full, differential, log backup jobs as I have done above or if they
should be run seperatly as one or more standalone jobs on some frequency. Is
there any info on best practices and/or examples on how to handle these
other job tasks?
It depends on the size of your databases and your backup window.
For example if you have several databases of only a few gigabytes each one
perhaps you can do full backups daily. If your databases are hundreds of
gigabytes the backup procedure you show make more sense.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:

> I am not a SQL Server DBA, but have a temporary need to play that role. I am
> new to the Maintenance Plan Wizard, but have thus far implemented the
> following:
> 1. Daily System Full Backup Job (Selected System Databases)
> 1.1. Check Database Integrity
> 1.2. Rebuild Index
> 1.3. Update Statistics
> 1.4. Clean Up History
> 1.5. Backup Database (Full)
> 1.6. Runs daily at 6:15AM
> 2. Weekly User Full Backup Job (Selected All User Databases)
> 2.1. Check Database Integrity
> 2.2. Rebuild Index
> 3.3. Update Statistics
> 3.4. Clean Up History
> 3.5. Backup Database (Full)
> 3.6. Runs every Saturday at 6:15AM
> 3. Daily User Differential Backup Job (Selected All User Databases)
> 3.1. Check Database Integrity
> 3.2. Backup Database (Differential)
> 3.3. Runs Sun thru Fri at 6:15AM
> 4. Hourly User Log Backup Job (Selected All User Databases)
> 4.1. Backup Database (Transaction Log)
> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> This doesn't seem quite right to me. I am mostly confused by the additional
> maintance options in the Wizard (Check Database Integrity, Shrink Database,
> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
> Execute SQL Server Agent Job). I am wondering if these should be included in
> the full, differential, log backup jobs as I have done above or if they
> should be run seperatly as one or more standalone jobs on some frequency. Is
> there any info on best practices and/or examples on how to handle these
> other job tasks?
>
>
|||What I am not sure of is the other options, such as index maintenance, etc.
Should they be run as standalone jobs... and how do I select which to run.
For example, reorganize index and rebuild index do not seem to be candidates
to run together in a single job and I have heard that it is probably ot a
good idea to run Shrink Database at all.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...[vbcol=seagreen]
> It depends on the size of your databases and your backup window.
> For example if you have several databases of only a few gigabytes each one
> perhaps you can do full backups daily. If your databases are hundreds of
> gigabytes the backup procedure you show make more sense.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
|||Everything is "it depends".
Do not shrink your databases in a job and avoid it even manually.
Do not run reorganize index and rebuild index together. Most important, do
you really need to reindex? Do you have a fragmentation problem? How big are
your databases? How big are your tables? Spent some time learning about dbcc
showcontig.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:

> What I am not sure of is the other options, such as index maintenance, etc.
> Should they be run as standalone jobs... and how do I select which to run.
> For example, reorganize index and rebuild index do not seem to be candidates
> to run together in a single job and I have heard that it is probably ot a
> good idea to run Shrink Database at all.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
>
>
|||Ok, tweaked it some more and the plans are looking cleaner.
Now I am wondering about History Cleanup... does that clean all outdated
backup files from system and user?
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...[vbcol=seagreen]
> Everything is "it depends".
> Do not shrink your databases in a job and avoid it even manually.
> Do not run reorganize index and rebuild index together. Most important, do
> you really need to reindex? Do you have a fragmentation problem? How big
> are
> your databases? How big are your tables? Spent some time learning about
> dbcc
> showcontig.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
|||This cleans up information from the msdb database like backup and restore
history, jobs history and maintenace plan history.
It is up to you how much data you want to keep.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:

> Ok, tweaked it some more and the plans are looking cleaner.
> Now I am wondering about History Cleanup... does that clean all outdated
> backup files from system and user?
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
>
>
|||It seems rather pointless to keep history data on backup files that have
long since been purged. Around here, we keep our customers backups on disk
that are required to erstore from the last full backup, and everythingis on
tape for 31 days. Anything older than that is gone and purged in our jobs
using Sp_delete_backuphistory
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D532E1BC-E544-46F1-BBBD-FF789A528EBC@.microsoft.com...[vbcol=seagreen]
> This cleans up information from the msdb database like backup and restore
> history, jobs history and maintenace plan history.
> It is up to you how much data you want to keep.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:

One more question on best-practice for Backup Plan

I am not a SQL Server DBA, but have a temporary need to play that role. I am
new to the Maintenance Plan Wizard, but have thus far implemented the
following:
1. Daily System Full Backup Job (Selected System Databases)
1.1. Check Database Integrity
1.2. Rebuild Index
1.3. Update Statistics
1.4. Clean Up History
1.5. Backup Database (Full)
1.6. Runs daily at 6:15AM
2. Weekly User Full Backup Job (Selected All User Databases)
2.1. Check Database Integrity
2.2. Rebuild Index
3.3. Update Statistics
3.4. Clean Up History
3.5. Backup Database (Full)
3.6. Runs every Saturday at 6:15AM
3. Daily User Differential Backup Job (Selected All User Databases)
3.1. Check Database Integrity
3.2. Backup Database (Differential)
3.3. Runs Sun thru Fri at 6:15AM
4. Hourly User Log Backup Job (Selected All User Databases)
4.1. Backup Database (Transaction Log)
4.1. Runs Hourly Sun thru Sat at 6:00AM
This doesn't seem quite right to me. I am mostly confused by the additional
maintance options in the Wizard (Check Database Integrity, Shrink Database,
Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
Execute SQL Server Agent Job). I am wondering if these should be included in
the full, differential, log backup jobs as I have done above or if they
should be run seperatly as one or more standalone jobs on some frequency. Is
there any info on best practices and/or examples on how to handle these
other job tasks?It depends on the size of your databases and your backup window.
For example if you have several databases of only a few gigabytes each one
perhaps you can do full backups daily. If your databases are hundreds of
gigabytes the backup procedure you show make more sense.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> I am not a SQL Server DBA, but have a temporary need to play that role. I am
> new to the Maintenance Plan Wizard, but have thus far implemented the
> following:
> 1. Daily System Full Backup Job (Selected System Databases)
> 1.1. Check Database Integrity
> 1.2. Rebuild Index
> 1.3. Update Statistics
> 1.4. Clean Up History
> 1.5. Backup Database (Full)
> 1.6. Runs daily at 6:15AM
> 2. Weekly User Full Backup Job (Selected All User Databases)
> 2.1. Check Database Integrity
> 2.2. Rebuild Index
> 3.3. Update Statistics
> 3.4. Clean Up History
> 3.5. Backup Database (Full)
> 3.6. Runs every Saturday at 6:15AM
> 3. Daily User Differential Backup Job (Selected All User Databases)
> 3.1. Check Database Integrity
> 3.2. Backup Database (Differential)
> 3.3. Runs Sun thru Fri at 6:15AM
> 4. Hourly User Log Backup Job (Selected All User Databases)
> 4.1. Backup Database (Transaction Log)
> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> This doesn't seem quite right to me. I am mostly confused by the additional
> maintance options in the Wizard (Check Database Integrity, Shrink Database,
> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
> Execute SQL Server Agent Job). I am wondering if these should be included in
> the full, differential, log backup jobs as I have done above or if they
> should be run seperatly as one or more standalone jobs on some frequency. Is
> there any info on best practices and/or examples on how to handle these
> other job tasks?
>
>|||What I am not sure of is the other options, such as index maintenance, etc.
Should they be run as standalone jobs... and how do I select which to run.
For example, reorganize index and rebuild index do not seem to be candidates
to run together in a single job and I have heard that it is probably ot a
good idea to run Shrink Database at all.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> It depends on the size of your databases and your backup window.
> For example if you have several databases of only a few gigabytes each one
> perhaps you can do full backups daily. If your databases are hundreds of
> gigabytes the backup procedure you show make more sense.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> I am not a SQL Server DBA, but have a temporary need to play that role. I
>> am
>> new to the Maintenance Plan Wizard, but have thus far implemented the
>> following:
>> 1. Daily System Full Backup Job (Selected System Databases)
>> 1.1. Check Database Integrity
>> 1.2. Rebuild Index
>> 1.3. Update Statistics
>> 1.4. Clean Up History
>> 1.5. Backup Database (Full)
>> 1.6. Runs daily at 6:15AM
>> 2. Weekly User Full Backup Job (Selected All User Databases)
>> 2.1. Check Database Integrity
>> 2.2. Rebuild Index
>> 3.3. Update Statistics
>> 3.4. Clean Up History
>> 3.5. Backup Database (Full)
>> 3.6. Runs every Saturday at 6:15AM
>> 3. Daily User Differential Backup Job (Selected All User Databases)
>> 3.1. Check Database Integrity
>> 3.2. Backup Database (Differential)
>> 3.3. Runs Sun thru Fri at 6:15AM
>> 4. Hourly User Log Backup Job (Selected All User Databases)
>> 4.1. Backup Database (Transaction Log)
>> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> This doesn't seem quite right to me. I am mostly confused by the
>> additional
>> maintance options in the Wizard (Check Database Integrity, Shrink
>> Database,
>> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
>> Execute SQL Server Agent Job). I am wondering if these should be included
>> in
>> the full, differential, log backup jobs as I have done above or if they
>> should be run seperatly as one or more standalone jobs on some frequency.
>> Is
>> there any info on best practices and/or examples on how to handle these
>> other job tasks?
>>|||Everything is "it depends".
Do not shrink your databases in a job and avoid it even manually.
Do not run reorganize index and rebuild index together. Most important, do
you really need to reindex? Do you have a fragmentation problem? How big are
your databases? How big are your tables? Spent some time learning about dbcc
showcontig.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> What I am not sure of is the other options, such as index maintenance, etc.
> Should they be run as standalone jobs... and how do I select which to run.
> For example, reorganize index and rebuild index do not seem to be candidates
> to run together in a single job and I have heard that it is probably ot a
> good idea to run Shrink Database at all.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> >
> > It depends on the size of your databases and your backup window.
> >
> > For example if you have several databases of only a few gigabytes each one
> > perhaps you can do full backups daily. If your databases are hundreds of
> > gigabytes the backup procedure you show make more sense.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Bill Fuller" wrote:
> >
> >> I am not a SQL Server DBA, but have a temporary need to play that role. I
> >> am
> >> new to the Maintenance Plan Wizard, but have thus far implemented the
> >> following:
> >>
> >> 1. Daily System Full Backup Job (Selected System Databases)
> >> 1.1. Check Database Integrity
> >> 1.2. Rebuild Index
> >> 1.3. Update Statistics
> >> 1.4. Clean Up History
> >> 1.5. Backup Database (Full)
> >> 1.6. Runs daily at 6:15AM
> >>
> >> 2. Weekly User Full Backup Job (Selected All User Databases)
> >> 2.1. Check Database Integrity
> >> 2.2. Rebuild Index
> >> 3.3. Update Statistics
> >> 3.4. Clean Up History
> >> 3.5. Backup Database (Full)
> >> 3.6. Runs every Saturday at 6:15AM
> >>
> >> 3. Daily User Differential Backup Job (Selected All User Databases)
> >> 3.1. Check Database Integrity
> >> 3.2. Backup Database (Differential)
> >> 3.3. Runs Sun thru Fri at 6:15AM
> >>
> >> 4. Hourly User Log Backup Job (Selected All User Databases)
> >> 4.1. Backup Database (Transaction Log)
> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> >>
> >> This doesn't seem quite right to me. I am mostly confused by the
> >> additional
> >> maintance options in the Wizard (Check Database Integrity, Shrink
> >> Database,
> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, and
> >> Execute SQL Server Agent Job). I am wondering if these should be included
> >> in
> >> the full, differential, log backup jobs as I have done above or if they
> >> should be run seperatly as one or more standalone jobs on some frequency.
> >> Is
> >> there any info on best practices and/or examples on how to handle these
> >> other job tasks?
> >>
> >>
> >>
>
>|||Ok, tweaked it some more and the plans are looking cleaner.
Now I am wondering about History Cleanup... does that clean all outdated
backup files from system and user?
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
> Everything is "it depends".
> Do not shrink your databases in a job and avoid it even manually.
> Do not run reorganize index and rebuild index together. Most important, do
> you really need to reindex? Do you have a fragmentation problem? How big
> are
> your databases? How big are your tables? Spent some time learning about
> dbcc
> showcontig.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> What I am not sure of is the other options, such as index maintenance,
>> etc.
>> Should they be run as standalone jobs... and how do I select which to
>> run.
>> For example, reorganize index and rebuild index do not seem to be
>> candidates
>> to run together in a single job and I have heard that it is probably ot a
>> good idea to run Shrink Database at all.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
>> >
>> > It depends on the size of your databases and your backup window.
>> >
>> > For example if you have several databases of only a few gigabytes each
>> > one
>> > perhaps you can do full backups daily. If your databases are hundreds
>> > of
>> > gigabytes the backup procedure you show make more sense.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Bill Fuller" wrote:
>> >
>> >> I am not a SQL Server DBA, but have a temporary need to play that
>> >> role. I
>> >> am
>> >> new to the Maintenance Plan Wizard, but have thus far implemented the
>> >> following:
>> >>
>> >> 1. Daily System Full Backup Job (Selected System Databases)
>> >> 1.1. Check Database Integrity
>> >> 1.2. Rebuild Index
>> >> 1.3. Update Statistics
>> >> 1.4. Clean Up History
>> >> 1.5. Backup Database (Full)
>> >> 1.6. Runs daily at 6:15AM
>> >>
>> >> 2. Weekly User Full Backup Job (Selected All User Databases)
>> >> 2.1. Check Database Integrity
>> >> 2.2. Rebuild Index
>> >> 3.3. Update Statistics
>> >> 3.4. Clean Up History
>> >> 3.5. Backup Database (Full)
>> >> 3.6. Runs every Saturday at 6:15AM
>> >>
>> >> 3. Daily User Differential Backup Job (Selected All User Databases)
>> >> 3.1. Check Database Integrity
>> >> 3.2. Backup Database (Differential)
>> >> 3.3. Runs Sun thru Fri at 6:15AM
>> >>
>> >> 4. Hourly User Log Backup Job (Selected All User Databases)
>> >> 4.1. Backup Database (Transaction Log)
>> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> >>
>> >> This doesn't seem quite right to me. I am mostly confused by the
>> >> additional
>> >> maintance options in the Wizard (Check Database Integrity, Shrink
>> >> Database,
>> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History,
>> >> and
>> >> Execute SQL Server Agent Job). I am wondering if these should be
>> >> included
>> >> in
>> >> the full, differential, log backup jobs as I have done above or if
>> >> they
>> >> should be run seperatly as one or more standalone jobs on some
>> >> frequency.
>> >> Is
>> >> there any info on best practices and/or examples on how to handle
>> >> these
>> >> other job tasks?
>> >>
>> >>
>> >>
>>|||This cleans up information from the msdb database like backup and restore
history, jobs history and maintenace plan history.
It is up to you how much data you want to keep.
Hope this helps,
Ben Nevarez
"Bill Fuller" wrote:
> Ok, tweaked it some more and the plans are looking cleaner.
> Now I am wondering about History Cleanup... does that clean all outdated
> backup files from system and user?
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
> >
> > Everything is "it depends".
> >
> > Do not shrink your databases in a job and avoid it even manually.
> >
> > Do not run reorganize index and rebuild index together. Most important, do
> > you really need to reindex? Do you have a fragmentation problem? How big
> > are
> > your databases? How big are your tables? Spent some time learning about
> > dbcc
> > showcontig.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Bill Fuller" wrote:
> >
> >> What I am not sure of is the other options, such as index maintenance,
> >> etc.
> >> Should they be run as standalone jobs... and how do I select which to
> >> run.
> >> For example, reorganize index and rebuild index do not seem to be
> >> candidates
> >> to run together in a single job and I have heard that it is probably ot a
> >> good idea to run Shrink Database at all.
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
> >> >
> >> > It depends on the size of your databases and your backup window.
> >> >
> >> > For example if you have several databases of only a few gigabytes each
> >> > one
> >> > perhaps you can do full backups daily. If your databases are hundreds
> >> > of
> >> > gigabytes the backup procedure you show make more sense.
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> >
> >> >
> >> >
> >> >
> >> > "Bill Fuller" wrote:
> >> >
> >> >> I am not a SQL Server DBA, but have a temporary need to play that
> >> >> role. I
> >> >> am
> >> >> new to the Maintenance Plan Wizard, but have thus far implemented the
> >> >> following:
> >> >>
> >> >> 1. Daily System Full Backup Job (Selected System Databases)
> >> >> 1.1. Check Database Integrity
> >> >> 1.2. Rebuild Index
> >> >> 1.3. Update Statistics
> >> >> 1.4. Clean Up History
> >> >> 1.5. Backup Database (Full)
> >> >> 1.6. Runs daily at 6:15AM
> >> >>
> >> >> 2. Weekly User Full Backup Job (Selected All User Databases)
> >> >> 2.1. Check Database Integrity
> >> >> 2.2. Rebuild Index
> >> >> 3.3. Update Statistics
> >> >> 3.4. Clean Up History
> >> >> 3.5. Backup Database (Full)
> >> >> 3.6. Runs every Saturday at 6:15AM
> >> >>
> >> >> 3. Daily User Differential Backup Job (Selected All User Databases)
> >> >> 3.1. Check Database Integrity
> >> >> 3.2. Backup Database (Differential)
> >> >> 3.3. Runs Sun thru Fri at 6:15AM
> >> >>
> >> >> 4. Hourly User Log Backup Job (Selected All User Databases)
> >> >> 4.1. Backup Database (Transaction Log)
> >> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
> >> >>
> >> >> This doesn't seem quite right to me. I am mostly confused by the
> >> >> additional
> >> >> maintance options in the Wizard (Check Database Integrity, Shrink
> >> >> Database,
> >> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up History,
> >> >> and
> >> >> Execute SQL Server Agent Job). I am wondering if these should be
> >> >> included
> >> >> in
> >> >> the full, differential, log backup jobs as I have done above or if
> >> >> they
> >> >> should be run seperatly as one or more standalone jobs on some
> >> >> frequency.
> >> >> Is
> >> >> there any info on best practices and/or examples on how to handle
> >> >> these
> >> >> other job tasks?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It seems rather pointless to keep history data on backup files that have
long since been purged. Around here, we keep our customers backups on disk
that are required to erstore from the last full backup, and everythingis on
tape for 31 days. Anything older than that is gone and purged in our jobs
using Sp_delete_backuphistory
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D532E1BC-E544-46F1-BBBD-FF789A528EBC@.microsoft.com...
> This cleans up information from the msdb database like backup and restore
> history, jobs history and maintenace plan history.
> It is up to you how much data you want to keep.
> Hope this helps,
> Ben Nevarez
>
>
> "Bill Fuller" wrote:
>> Ok, tweaked it some more and the plans are looking cleaner.
>> Now I am wondering about History Cleanup... does that clean all outdated
>> backup files from system and user?
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:9D0D7882-AE82-4D60-8524-B9A55F9F5C95@.microsoft.com...
>> >
>> > Everything is "it depends".
>> >
>> > Do not shrink your databases in a job and avoid it even manually.
>> >
>> > Do not run reorganize index and rebuild index together. Most important,
>> > do
>> > you really need to reindex? Do you have a fragmentation problem? How
>> > big
>> > are
>> > your databases? How big are your tables? Spent some time learning about
>> > dbcc
>> > showcontig.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Bill Fuller" wrote:
>> >
>> >> What I am not sure of is the other options, such as index maintenance,
>> >> etc.
>> >> Should they be run as standalone jobs... and how do I select which to
>> >> run.
>> >> For example, reorganize index and rebuild index do not seem to be
>> >> candidates
>> >> to run together in a single job and I have heard that it is probably
>> >> ot a
>> >> good idea to run Shrink Database at all.
>> >>
>> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> >> news:FE7E7F0F-7605-4724-8AC7-5A6BB7B4F3F4@.microsoft.com...
>> >> >
>> >> > It depends on the size of your databases and your backup window.
>> >> >
>> >> > For example if you have several databases of only a few gigabytes
>> >> > each
>> >> > one
>> >> > perhaps you can do full backups daily. If your databases are
>> >> > hundreds
>> >> > of
>> >> > gigabytes the backup procedure you show make more sense.
>> >> >
>> >> > Hope this helps,
>> >> >
>> >> > Ben Nevarez
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Bill Fuller" wrote:
>> >> >
>> >> >> I am not a SQL Server DBA, but have a temporary need to play that
>> >> >> role. I
>> >> >> am
>> >> >> new to the Maintenance Plan Wizard, but have thus far implemented
>> >> >> the
>> >> >> following:
>> >> >>
>> >> >> 1. Daily System Full Backup Job (Selected System Databases)
>> >> >> 1.1. Check Database Integrity
>> >> >> 1.2. Rebuild Index
>> >> >> 1.3. Update Statistics
>> >> >> 1.4. Clean Up History
>> >> >> 1.5. Backup Database (Full)
>> >> >> 1.6. Runs daily at 6:15AM
>> >> >>
>> >> >> 2. Weekly User Full Backup Job (Selected All User Databases)
>> >> >> 2.1. Check Database Integrity
>> >> >> 2.2. Rebuild Index
>> >> >> 3.3. Update Statistics
>> >> >> 3.4. Clean Up History
>> >> >> 3.5. Backup Database (Full)
>> >> >> 3.6. Runs every Saturday at 6:15AM
>> >> >>
>> >> >> 3. Daily User Differential Backup Job (Selected All User Databases)
>> >> >> 3.1. Check Database Integrity
>> >> >> 3.2. Backup Database (Differential)
>> >> >> 3.3. Runs Sun thru Fri at 6:15AM
>> >> >>
>> >> >> 4. Hourly User Log Backup Job (Selected All User Databases)
>> >> >> 4.1. Backup Database (Transaction Log)
>> >> >> 4.1. Runs Hourly Sun thru Sat at 6:00AM
>> >> >>
>> >> >> This doesn't seem quite right to me. I am mostly confused by the
>> >> >> additional
>> >> >> maintance options in the Wizard (Check Database Integrity, Shrink
>> >> >> Database,
>> >> >> Reorganize Index, Rebuild Index, Update Statistics, Clean Up
>> >> >> History,
>> >> >> and
>> >> >> Execute SQL Server Agent Job). I am wondering if these should be
>> >> >> included
>> >> >> in
>> >> >> the full, differential, log backup jobs as I have done above or if
>> >> >> they
>> >> >> should be run seperatly as one or more standalone jobs on some
>> >> >> frequency.
>> >> >> Is
>> >> >> there any info on best practices and/or examples on how to handle
>> >> >> these
>> >> >> other job tasks?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Old transaction log is reported as missing during recovery

I've done this at least 50 times without issue on various databases, but this
one just doesn't want to play nice.
So I have a SQLServer2K database setup. I've got a home-grown disaster
recovery process in place, so that transactions are taken from one server and
migrated to a backup server every hour. Once it's up and running, the
process is pretty flawless. However, getting it setup can be tricky.
1) Set the recovery model to "Full".
2) Backup your transaction logs
3) Do a full backup
4) Copy the backup to the backup server
5) Restore in to a read only database
Transaction logs can now be backed up on the source database, copied over
and applied to the backup server.
This isn't working this time though. No matter what I've tried, I still get
a message during the restore of the transaction logs such as the following:
"Executed as user: dbo. The log in this backup set begins at LSN
83111000001460600001, which is too late to apply to the database. An earlier
log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed."
Given the large gap between the two LSN #'s, I'm assuming that the database
thinks there is a very old transaction I'm missing. However, that is sheer
guesswork on my part.
The source box has been cycled, I've tried turning the recovery back and
forth between Full and Simple. This error is from my latest attempt, when I
swear I've done everything by the book.
Any suggestions of what might be causing this or where I should go from here?
Fred...
SQL lost a log somehow, I often had problems similar to this w/ home grown
log shipping.
Solution mostly relied upon: re-execute full backup, then restore & fire up
log shipping again. Full backup restores should be done nightly if
affordable...
Also - transactional replication ultimately proved the best solution for us,
transactions replicated within seconds, if we ever had to failover to backup,
I am much more confident minimal data would be lost as opposed to hourly log
restores...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"fkutz" wrote:

> I've done this at least 50 times without issue on various databases, but this
> one just doesn't want to play nice.
> So I have a SQLServer2K database setup. I've got a home-grown disaster
> recovery process in place, so that transactions are taken from one server and
> migrated to a backup server every hour. Once it's up and running, the
> process is pretty flawless. However, getting it setup can be tricky.
> 1) Set the recovery model to "Full".
> 2) Backup your transaction logs
> 3) Do a full backup
> 4) Copy the backup to the backup server
> 5) Restore in to a read only database
> Transaction logs can now be backed up on the source database, copied over
> and applied to the backup server.
> This isn't working this time though. No matter what I've tried, I still get
> a message during the restore of the transaction logs such as the following:
> "Executed as user: dbo. The log in this backup set begins at LSN
> 83111000001460600001, which is too late to apply to the database. An earlier
> log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
> 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
> (Error 3013). The step failed."
> Given the large gap between the two LSN #'s, I'm assuming that the database
> thinks there is a very old transaction I'm missing. However, that is sheer
> guesswork on my part.
> The source box has been cycled, I've tried turning the recovery back and
> forth between Full and Simple. This error is from my latest attempt, when I
> swear I've done everything by the book.
> Any suggestions of what might be causing this or where I should go from here?
> Fred...