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:
Showing posts with label wizard. Show all posts
Showing posts with label wizard. Show all posts
Friday, March 30, 2012
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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
Friday, March 23, 2012
OMWWIZE and Copy Database Wizard
Please help.
I am trying to move one of our production databases (MSSQL7) to a larger,
more powerful box running MSSQL2000. Each time I run the copy database
wizard, I get a failure with the error message reading "Failed to create the
share OMWWIZE."
What is this share? Why do I need it? Why would this step fail? Where should
it be? How do I fix this?
I say again, please help.
--
Kerry CarrollHave a look here
http://support.microsoft.com/default.aspx?scid=kb;en-us;q274463
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Good Try" <test@.not.com> wrote in message
news:eL59ym$vDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Please help.
> I am trying to move one of our production databases (MSSQL7) to a larger,
> more powerful box running MSSQL2000. Each time I run the copy database
> wizard, I get a failure with the error message reading "Failed to create
the
> share OMWWIZE."
> What is this share? Why do I need it? Why would this step fail? Where
should
> it be? How do I fix this?
> I say again, please help.
> --
> Kerry Carroll
>
I am trying to move one of our production databases (MSSQL7) to a larger,
more powerful box running MSSQL2000. Each time I run the copy database
wizard, I get a failure with the error message reading "Failed to create the
share OMWWIZE."
What is this share? Why do I need it? Why would this step fail? Where should
it be? How do I fix this?
I say again, please help.
--
Kerry CarrollHave a look here
http://support.microsoft.com/default.aspx?scid=kb;en-us;q274463
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Good Try" <test@.not.com> wrote in message
news:eL59ym$vDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Please help.
> I am trying to move one of our production databases (MSSQL7) to a larger,
> more powerful box running MSSQL2000. Each time I run the copy database
> wizard, I get a failure with the error message reading "Failed to create
the
> share OMWWIZE."
> What is this share? Why do I need it? Why would this step fail? Where
should
> it be? How do I fix this?
> I say again, please help.
> --
> Kerry Carroll
>
OLTP vs OLAP (Data Warehouse) as a DataSource in Analysis Services 2005
Hello,
Can I import an OLTP (Reltional DB) as a Data Source into SQL Server
Analysis Services 2005 and then use the Cube Wizard and the new Data
Source View feature to create the OLAP model ?
Or do I have to first design an OLAP Data Warehouse with a Star Schema
and then import this DW as a Data Source into my Analysis Services
Project.
With SQL Server 2000 , OLAP would be the way to go..but with SQL
Server 2005 , it seems as though the wizard and data source view
features do half the work for you.
I have an OLTP DB and am not sure which route I should take ! Any
suggestions / input would be much appreciated.
Thanks in Advance...
Regards
RusszeeHere's the followup answer to my own post...
Just what I thought...It's the new UDM all the way in SQL Server
2005 !!!
"SQL Server 2005 Analysis Services can create UDM cubes without the
intermediate step of building a star schema data warehouse. The UDM
enables organizations to build reporting applications directly against
a production system and doesn't require an intermediate data warehouse
as in the past. This is because the UDM doesn't require the data to be
in a star or snowflake schema, but can connect to any data source
whose data is stored in a third normal form. "
Source : http://www.microsoft.com/technet/pr...5/solvngbp.mspx
Tuesday, March 20, 2012
OLEDB error while processing Analysis service database
Hi,
I migrated AS 2000 database to AS 2005 thorugh migration wizard and started processing the database. But I am getting error like "OLE DB error: OLE DB or ODBC error: Query (5, 19) Parser: The syntax for 'AS' is incorrect..". After migrating I didn't modified any settings.
If anybody having info, please share.
ThanksIt looks the SQL query sent by Analysis Services for processing is bad. You can examine the query in the processing dialog and see what's wrong (wrong syntax for AS clause?).
I migrated AS 2000 database to AS 2005 thorugh migration wizard and started processing the database. But I am getting error like "OLE DB error: OLE DB or ODBC error: Query (5, 19) Parser: The syntax for 'AS' is incorrect..". After migrating I didn't modified any settings.
If anybody having info, please share.
ThanksIt looks the SQL query sent by Analysis Services for processing is bad. You can examine the query in the processing dialog and see what's wrong (wrong syntax for AS clause?).
Monday, March 12, 2012
OLEDB Bookmarks
I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
1. CBookmark<4> m_bookmark; // member variable
2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property
3. BOOKMARK_ENTRY(m_bookmark) // column map entry
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
Any ideas?
I guess you are doing the following when you modify:
Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)
Hope this works
|||Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:
#define BOOKMARK_ENTRY_STATUS(variable, status) \
COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)
Thanks for the ideas. It set me on the proper path to the solution.
OLEDB Bookmarks
I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
1. CBookmark<4> m_bookmark; // member variable
2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property
3. BOOKMARK_ENTRY(m_bookmark) // column map entry
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
Any ideas?
I guess you are doing the following when you modify:
Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)
Hope this works
|||Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:
#define BOOKMARK_ENTRY_STATUS(variable, status) \
COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)
Thanks for the ideas. It set me on the proper path to the solution.
OLEDB Bookmarks
I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
1. CBookmark<4> m_bookmark; // member variable
2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property
3. BOOKMARK_ENTRY(m_bookmark) // column map entry
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
Any ideas?
I guess you are doing the following when you modify:
Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)
Hope this works
|||Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:
#define BOOKMARK_ENTRY_STATUS(variable, status) \
COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)
Thanks for the ideas. It set me on the proper path to the solution.
Subscribe to:
Posts (Atom)