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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

One more easy one.

What's the use of Filegroup backup?
Let's say we have around 60 tables. 30 in one filegroup
rest are in another filegroup.
What's the application of backing up only filegroup
instead of whole databse.
Because in any situation if we have to restore WHOLE
database. We can't just restore just 30 tables (in 1
filegroup) we have to restore the WHOLE database.
How would the integrity of databse is mainained if we
restore from filegroup datbase of filegroup1 taken at 2 pm
and restore from filgroup datbaase of filegroup2 taken at
3 pm.The main advantage of filegroup backup is for very large databases. You can
back up only the required parts. Together with log backups you can the
restore the filegroup backup and reapply the logs to bring everything back
to a consistent point.
So very useful if the size gets really big. In most circumstances full
backups and appropriate log backups are probably fine.
Mike John
<anonymous@.discussions.microsoft.com> wrote in message
news:00bf01c49fff$e77d9360$a401280a@.phx.gbl...
> What's the use of Filegroup backup?
> Let's say we have around 60 tables. 30 in one filegroup
> rest are in another filegroup.
> What's the application of backing up only filegroup
> instead of whole databse.
> Because in any situation if we have to restore WHOLE
> database. We can't just restore just 30 tables (in 1
> filegroup) we have to restore the WHOLE database.
> How would the integrity of databse is mainained if we
> restore from filegroup datbase of filegroup1 taken at 2 pm
> and restore from filgroup datbaase of filegroup2 taken at
> 3 pm.
>

On the subject of backup plans ...

Given the situation of a daily full backup and shrink of a database, does
anyone know of any 'gotchas' that are likely to bite one in the backside
when the recovery model for the databse is 'Full' as opposed to 'Simple'.
For example:
use master;
backup database <databasename> to disk='<filename>' with description='Full
backup of <databasename>',init,skip;
dbcc shrinkdatabase(<databasename>)When you set your database's recovery model to FULL then your passive
virtual log files will be held until you backup the database.
FULL recovery model is recommended for production environments because then
you could restore your database to the point of failure. Also you'd be able
to backup Transaction Logs.
If you set it's rec. model to SIMPLE then passive vlfs will be truncated at
every checkpoint. This recovery model is recommended for test environments
and if you use this recovery model then you'd not be able to transaction log
files. So you'd be able to restore your database only using your FULL and
DIFFERENTIAL backups.
Not using Transaction Log File backups is unacceptable for most of the
production environments.
Also, shrinking a database all the time is not a good idea for every
situation. This could be a performance problem as your database would need
to expand when it is being used. Expending and shrinking is a resource
consuming process.
Overview of the Recovery Models:
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||Yes, yes ... I know all that!
So you are not aware of any 'gotchas' for the situation I described then?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
> When you set your database's recovery model to FULL then your passive
> virtual log files will be held until you backup the database.
> FULL recovery model is recommended for production environments because
> then you could restore your database to the point of failure. Also you'd
> be able to backup Transaction Logs.
> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
> at every checkpoint. This recovery model is recommended for test
> environments and if you use this recovery model then you'd not be able to
> transaction log files. So you'd be able to restore your database only
> using your FULL and DIFFERENTIAL backups.
> Not using Transaction Log File backups is unacceptable for most of the
> production environments.
> Also, shrinking a database all the time is not a good idea for every
> situation. This could be a performance problem as your database would need
> to expand when it is being used. Expending and shrinking is a resource
> consuming process.
> Overview of the Recovery Models:
> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Stephany Young" <noone@.localhost> wrote in message
> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does
>> anyone know of any 'gotchas' that are likely to bite one in the backside
>> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Not interested in your subject anymore.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||> So you are not aware of any 'gotchas' for the situation I described then?
Shrinking files every night is bad because of the performance hit that
occurs when files grow back to the needed size during the day. In the FULL
recovery model, the logs should be sized to accommodate expected activity
between log backups. The goal is to avoid file growth during normal
activity while keep file sizes manageable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Thanks for that Dan.
Maybe I phrased my original question badly.
I think the question should have been:
Is anyone aware of any behavioural differences between executing a 'dbcc
shrinkdatabase(<databasename>)' on a Full Recovery Model database compared
to executing the same statement on the same database if it were using Simple
Recovery Model?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
> Shrinking files every night is bad because of the performance hit that
> occurs when files grow back to the needed size during the day. In the
> FULL recovery model, the logs should be sized to accommodate expected
> activity between log backups. The goal is to avoid file growth during
> normal activity while keep file sizes manageable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephany Young" <noone@.localhost> wrote in message
> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able
>> to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed
>> to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>|||Ya, I got "gotcha" for ya.
When you shrink a DB (Simple, BulkLogged, or Full), watch the effect on the
filesystem in terms of drive fragmentation (outside of SQL Server).
Use the Windows defrag tool to analyze the partition your .MDF/.NDF files
are on and when it is done, look at the report for the .MDF/.NDF you shrunk.
It is likely the file will be fragmented on the disk, hindering overall
performance.
Since you specified 'shrinkdatabase', I'm assuming you're after .mdf/.ndf
files. The recovery model is dealing with the transaction log .ldf file. So
I guess the 'gotcha' is that you're operating on different areas.
I've never used 'dbcc shrinkdatabase', only 'dbcc shrinkfile' for out of
control log files (which relate more directly to the Recovery Model), so I
don't know the details of their differences.
Jay
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
For data files, the pages moved are logged to the transaction log. The shrink is not one
transaction, it is a lot of smaller transaction. This means that the log can be truncated (emptied)
during the shrink if you are in simple recovery.
And, just in case you didn't read: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stephany Young" <noone@.localhost> wrote in message news:%23aN8wbwFIHA.6068@.TK2MSFTNGP02.phx.gbl...
> Thanks for that Dan.
> Maybe I phrased my original question badly.
> I think the question should have been:
> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
>> Shrinking files every night is bad because of the performance hit that occurs when files grow
>> back to the needed size during the day. In the FULL recovery model, the logs should be sized to
>> accommodate expected activity between log backups. The goal is to avoid file growth during
>> normal activity while keep file sizes manageable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive virtual log files will be
>> held until you backup the database.
>> FULL recovery model is recommended for production environments because then you could restore
>> your database to the point of failure. Also you'd be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated at every checkpoint.
>> This recovery model is recommended for test environments and if you use this recovery model
>> then you'd not be able to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the production environments.
>> Also, shrinking a database all the time is not a good idea for every situation. This could be a
>> performance problem as your database would need to expand when it is being used. Expending and
>> shrinking is a resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does anyone know of any
>> 'gotchas' that are likely to bite one in the backside when the recovery model for the databse
>> is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with description='Full backup of
>> <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>

Omit Database from Backup

Let me first say that I am new to SQL so please be patient with me. I have a
test database on my SQL server that I do not wish to backup. Is there a way
to omit this database from my daily backup? I can't find an option that shows
which databases are getting backed up just and option to backup local server
or multiple servers. Thanks,
Carl
You need to tell us where you run this backup (what tool you are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Carl" <Carl@.discussions.microsoft.com> wrote in message
news:A5A9CC15-1C8B-4369-B6CD-C6D8E5E6F069@.microsoft.com...
> Let me first say that I am new to SQL so please be patient with me. I have a
> test database on my SQL server that I do not wish to backup. Is there a way
> to omit this database from my daily backup? I can't find an option that shows
> which databases are getting backed up just and option to backup local server
> or multiple servers. Thanks,
> Carl
|||Sorry, I am running SQL 2005 and I am using SQL Server Agent to back up the
database to a file. Then every night my backup software backs up the data and
transaction files that are created by SQL.
"Tibor Karaszi" wrote:

> You need to tell us where you run this backup (what tool you are using).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Carl" <Carl@.discussions.microsoft.com> wrote in message
> news:A5A9CC15-1C8B-4369-B6CD-C6D8E5E6F069@.microsoft.com...
>
|||> Sorry, I am running SQL 2005 and I am using SQL Server Agent to back up
> the
> database to a file.
What does that mean? Do you have a BACKUP DATABASE command in the step of
the job? Or are you using a maintenance plan?

> Then every night my backup software
What is "my backup software"?
|||Yes I am using a maintenance plan. I have 3 database one that is the old one
that we converted from to our new custom program, the live database and a
test database. I only want to backup the live database not the other two. I
have a maintenance plan that I am running every night at 7pm. This backup job
is under the Server Management Studio under the SQL Server Agent. On the
properties of the backup job there is a section called targets. The only
choice is Target local server. In the properties I don't see a place where I
can choose which database I am backing up. I figure it is backing up all
three but I am not sure, this is the information that I am trying to find
out.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does that mean? Do you have a BACKUP DATABASE command in the step of
> the job? Or are you using a maintenance plan?
>
> What is "my backup software"?
>
>
|||Thanks for the help but I have figured it out. I went under the management
folder then maintenance plans and then backup and found the settings that I
was looking for under the properties of the backup.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does that mean? Do you have a BACKUP DATABASE command in the step of
> the job? Or are you using a maintenance plan?
>
> What is "my backup software"?
>
>
sql

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in you
r
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 a
s
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in y
our
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
>

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIA
Wait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA
|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:
> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
> > Hi guys,
> > I find out that if you use DMP and change the backup path from default and
> > set the settings to delete old backup , say after 3 days, it doesnt really
> > delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> > well as in SQL2005
> >
> >
> > TIA

Old backup are not delete

I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running perfectly.
I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
But if I check in the directory were my backups reside, I still see the last
5 daily backup?
Is there something that I forgot?
Thanks
JPJP
Have you checked that an account under SQL Server Agent runs has appropriate
permissions to delete these files?
"JP Breton" <jpbreton@.videotron.ca> wrote in message
news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
> backups are running perfectly.
> I also checked in the database maintenance plan to the "Remove files older
> than: 1 day"
> But if I check in the directory were my backups reside, I still see the
last
> 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.ph
x.gbl...
>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running
>perfectly.
> I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
> But if I check in the directory were my backups reside, I still see the la
st 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Thanks Uri, thanks Tibor,
The SQL agent is ruuning with domain admin rights (I know it should not be
that way)
But I will still verify to make sure that the account can delete files.
Regards
JP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23fxRENZSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Below KB might help:
> l]
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from [url]www.sysinternals.com." target="_blank">http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JP Breton" <jpbreton@.videotron.ca> wrote in message
> news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>

Old backup are not delete

I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running perfectly.
I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
But if I check in the directory were my backups reside, I still see the last
5 daily backup?
Is there something that I forgot?
Thanks
JP
JP
Have you checked that an account under SQL Server Agent runs has appropriate
permissions to delete these files?
"JP Breton" <jpbreton@.videotron.ca> wrote in message
news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
> backups are running perfectly.
> I also checked in the database maintenance plan to the "Remove files older
> than: 1 day"
> But if I check in the directory were my backups reside, I still see the
last
> 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The backups are running
>perfectly.
> I also checked in the database maintenance plan to the "Remove files older than: 1 day"
> But if I check in the directory were my backups reside, I still see the last 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>
|||Thanks Uri, thanks Tibor,
The SQL agent is ruuning with domain admin rights (I know it should not be
that way)
But I will still verify to make sure that the account can delete files.
Regards
JP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23fxRENZSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JP Breton" <jpbreton@.videotron.ca> wrote in message
> news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>

Old backup are not delete

I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running perfectly.
I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
But if I check in the directory were my backups reside, I still see the last
5 daily backup?
Is there something that I forgot?
Thanks
JPJP
Have you checked that an account under SQL Server Agent runs has appropriate
permissions to delete these files?
"JP Breton" <jpbreton@.videotron.ca> wrote in message
news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
> backups are running perfectly.
> I also checked in the database maintenance plan to the "Remove files older
> than: 1 day"
> But if I check in the directory were my backups reside, I still see the
last
> 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The backups are running
>perfectly.
> I also checked in the database maintenance plan to the "Remove files older than: 1 day"
> But if I check in the directory were my backups reside, I still see the last 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Thanks Uri, thanks Tibor,
The SQL agent is ruuning with domain admin rights (I know it should not be
that way)
But I will still verify to make sure that the account can delete files.
Regards
JP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23fxRENZSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JP Breton" <jpbreton@.videotron.ca> wrote in message
> news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
>>backups are running perfectly.
>> I also checked in the database maintenance plan to the "Remove files
>> older than: 1 day"
>> But if I check in the directory were my backups reside, I still see the
>> last 5 daily backup?
>> Is there something that I forgot?
>> Thanks
>> JP
>>
>