Lowering priority: cleanup

Showing posts with label cleanup. Show all posts

Friday, March 30, 2012

Maintenance Plan Fails but I can't figure out why? No log informa

I have a maintenance plan that is failing but I'm not sure why. Plan
consists of:
1 Backup Database
2 Cleanup Task
3 Reorganize Index Task
4 Rebuild Index Task
5 Shrink Database Task
6 Update Statistics Task
I have a notification message configured for errors. For some reason I
receive two emails. One states success while the other states failure. On
the success email I do not see any mention of steps 3,4 or 6 (from above). I
cannot find any additional information in the logs. Below are the two
emails. These emails are sent out back to back. Thanks in advance for your
help.
Terry
###################################################
Success Email
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
was generated on "MSSQL".
Maintenance Plan: Database Backups
Duration: 08:26:44
Status: Succeeded.
Details:
Back Up Database (Full) (MSSQL)
Task start: 9/28/2007 9:00 PM.
Task end: 9/28/2007 9:07 PM.
Success
BAK Cleanup Task (MSSQL)
Task start: 9/28/2007 9:07 PM.
Task end: 9/28/2007 9:07 PM.
Success
Shrink Database Task (MSSQL)
Task start: 9/29/2007 3:33 AM.
Task end: 9/29/2007 3:35 AM.
Success
###################################################
Failed Email
OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
DURATION: 8 hours, 26 minutes, 51 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 1
(BackupDatabaseJob). The last step to run was step 1 (Subplan).Hello Terry!
Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
you will find the one which doesn't work. It stops your maintenance plan.
Besides, you better setup your maintenance plan one by one, not an "all in
one" package.
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>I have a maintenance plan that is failing but I'm not sure why. Plan
> consists of:
> 1 Backup Database
> 2 Cleanup Task
> 3 Reorganize Index Task
> 4 Rebuild Index Task
> 5 Shrink Database Task
> 6 Update Statistics Task
> I have a notification message configured for errors. For some reason I
> receive two emails. One states success while the other states failure.
> On
> the success email I do not see any mention of steps 3,4 or 6 (from above).
> I
> cannot find any additional information in the logs. Below are the two
> emails. These emails are sent out back to back. Thanks in advance for
> your
> help.
> Terry
> ###################################################
> Success Email
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> was generated on "MSSQL".
> Maintenance Plan: Database Backups
> Duration: 08:26:44
> Status: Succeeded.
> Details:
> Back Up Database (Full) (MSSQL)
> Task start: 9/28/2007 9:00 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> BAK Cleanup Task (MSSQL)
> Task start: 9/28/2007 9:07 PM.
> Task end: 9/28/2007 9:07 PM.
> Success
>
> Shrink Database Task (MSSQL)
> Task start: 9/29/2007 3:33 AM.
> Task end: 9/29/2007 3:35 AM.
> Success
>
> ###################################################
> Failed Email
> OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> DURATION: 8 hours, 26 minutes, 51 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 1
> (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>|||Hi Ekrem,
Thank you for the reply. I checked out the history logs for the job and
what is strange is that there are no entries? I right clicked on the job and
selected 'View Histories' but no entries. Can you think of a reason why I'm
not seeing any data?
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hello Terry!
>
> Check out Jobs' histories from the Jobs node under SQL Server Agent. There,
> you will find the one which doesn't work. It stops your maintenance plan.
> Besides, you better setup your maintenance plan one by one, not an "all in
> one" package.
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >I have a maintenance plan that is failing but I'm not sure why. Plan
> > consists of:
> > 1 Backup Database
> > 2 Cleanup Task
> > 3 Reorganize Index Task
> > 4 Rebuild Index Task
> > 5 Shrink Database Task
> > 6 Update Statistics Task
> >
> > I have a notification message configured for errors. For some reason I
> > receive two emails. One states success while the other states failure.
> > On
> > the success email I do not see any mention of steps 3,4 or 6 (from above).
> > I
> > cannot find any additional information in the logs. Below are the two
> > emails. These emails are sent out back to back. Thanks in advance for
> > your
> > help.
> >
> > Terry
> >
> > ###################################################
> > Success Email
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047 Report
> > was generated on "MSSQL".
> > Maintenance Plan: Database Backups
> > Duration: 08:26:44
> > Status: Succeeded.
> > Details:
> > Back Up Database (Full) (MSSQL)
> > Task start: 9/28/2007 9:00 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > BAK Cleanup Task (MSSQL)
> > Task start: 9/28/2007 9:07 PM.
> > Task end: 9/28/2007 9:07 PM.
> > Success
> >
> >
> > Shrink Database Task (MSSQL)
> > Task start: 9/29/2007 3:33 AM.
> > Task end: 9/29/2007 3:35 AM.
> > Success
> >
> >
> > ###################################################
> > Failed Email
> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> > DURATION: 8 hours, 26 minutes, 51 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >
>|||Hi Terry,
Could you tell me exactly what version and edition of SQL Server do you use?
First two reasons I can think of:
1- You did not select the checkbox just at the left side of the job name
from the Log File Viewer window.
2- The Jobs have never worked (in your case, some of them worked, so this
must not be valid for you)
P.S.
You use SSMS, not SSMSE right?
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> Hi Ekrem,
> Thank you for the reply. I checked out the history logs for the job and
> what is strange is that there are no entries? I right clicked on the job
> and
> selected 'View Histories' but no entries. Can you think of a reason why
> I'm
> not seeing any data?
> Thanks,
> Terry
> "Ekrem Ã?nsoy" wrote:
>> Hello Terry!
>>
>> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> There,
>> you will find the one which doesn't work. It stops your maintenance plan.
>> Besides, you better setup your maintenance plan one by one, not an "all
>> in
>> one" package.
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> > consists of:
>> > 1 Backup Database
>> > 2 Cleanup Task
>> > 3 Reorganize Index Task
>> > 4 Rebuild Index Task
>> > 5 Shrink Database Task
>> > 6 Update Statistics Task
>> >
>> > I have a notification message configured for errors. For some reason I
>> > receive two emails. One states success while the other states failure.
>> > On
>> > the success email I do not see any mention of steps 3,4 or 6 (from
>> > above).
>> > I
>> > cannot find any additional information in the logs. Below are the two
>> > emails. These emails are sent out back to back. Thanks in advance for
>> > your
>> > help.
>> >
>> > Terry
>> >
>> > ###################################################
>> > Success Email
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> > Report
>> > was generated on "MSSQL".
>> > Maintenance Plan: Database Backups
>> > Duration: 08:26:44
>> > Status: Succeeded.
>> > Details:
>> > Back Up Database (Full) (MSSQL)
>> > Task start: 9/28/2007 9:00 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > BAK Cleanup Task (MSSQL)
>> > Task start: 9/28/2007 9:07 PM.
>> > Task end: 9/28/2007 9:07 PM.
>> > Success
>> >
>> >
>> > Shrink Database Task (MSSQL)
>> > Task start: 9/29/2007 3:33 AM.
>> > Task end: 9/29/2007 3:35 AM.
>> > Success
>> >
>> >
>> > ###################################################
>> > Failed Email
>> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
>> > DURATION: 8 hours, 26 minutes, 51 seconds
>> > STATUS: Failed
>> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >|||Hi Ekrem,
Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
This is what I've did:
Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
Backups.Subplan) and selected 'View History'. When the dialog displays the
job is selected.
Thanks,
Terry
"Ekrem Ã?nsoy" wrote:
> Hi Terry,
>
> Could you tell me exactly what version and edition of SQL Server do you use?
> First two reasons I can think of:
> 1- You did not select the checkbox just at the left side of the job name
> from the Log File Viewer window.
> 2- The Jobs have never worked (in your case, some of them worked, so this
> must not be valid for you)
> P.S.
> You use SSMS, not SSMSE right?
>
> --
> Ekrem Ã?nsoy
>
> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
> > Hi Ekrem,
> > Thank you for the reply. I checked out the history logs for the job and
> > what is strange is that there are no entries? I right clicked on the job
> > and
> > selected 'View Histories' but no entries. Can you think of a reason why
> > I'm
> > not seeing any data?
> > Thanks,
> > Terry
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> Hello Terry!
> >>
> >>
> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
> >> There,
> >> you will find the one which doesn't work. It stops your maintenance plan.
> >>
> >> Besides, you better setup your maintenance plan one by one, not an "all
> >> in
> >> one" package.
> >>
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
> >> > consists of:
> >> > 1 Backup Database
> >> > 2 Cleanup Task
> >> > 3 Reorganize Index Task
> >> > 4 Rebuild Index Task
> >> > 5 Shrink Database Task
> >> > 6 Update Statistics Task
> >> >
> >> > I have a notification message configured for errors. For some reason I
> >> > receive two emails. One states success while the other states failure.
> >> > On
> >> > the success email I do not see any mention of steps 3,4 or 6 (from
> >> > above).
> >> > I
> >> > cannot find any additional information in the logs. Below are the two
> >> > emails. These emails are sent out back to back. Thanks in advance for
> >> > your
> >> > help.
> >> >
> >> > Terry
> >> >
> >> > ###################################################
> >> > Success Email
> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> >> > Report
> >> > was generated on "MSSQL".
> >> > Maintenance Plan: Database Backups
> >> > Duration: 08:26:44
> >> > Status: Succeeded.
> >> > Details:
> >> > Back Up Database (Full) (MSSQL)
> >> > Task start: 9/28/2007 9:00 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > BAK Cleanup Task (MSSQL)
> >> > Task start: 9/28/2007 9:07 PM.
> >> > Task end: 9/28/2007 9:07 PM.
> >> > Success
> >> >
> >> >
> >> > Shrink Database Task (MSSQL)
> >> > Task start: 9/29/2007 3:33 AM.
> >> > Task end: 9/29/2007 3:35 AM.
> >> > Success
> >> >
> >> >
> >> > ###################################################
> >> > Failed Email
> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01 PM
> >> > DURATION: 8 hours, 26 minutes, 51 seconds
> >> > STATUS: Failed
> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
> >> >
> >>
>|||Hi Terry!
Can you check the Filter setting in the Log Viewer window? If it's set to
some values then click Clear button in the Filter Settings window and click
OK.
Check if other logs are clear or do they have records from the Log File
Viewer? (SQL Agent logs for example)
If it is not your situation, then can you take a look at your Instance path?
Check if log files are stored or not.
For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Note:
I once faced this problem but in that situation the person who met this
problem was using SSMSE and when he started using SSMS he started seeing
logs.
--
Ekrem Ã?nsoy
"Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
news:0725AFDB-ED8A-4F3E-8712-EDAA3F097B15@.microsoft.com...
> Hi Ekrem,
> Using SQL Server 2005 (9.00.2047) and SQL Server Management Studio.
> This is what I've did:
> Expanded 'SQL Server Agent', Expanded Jobs, Right clicked on job (Database
> Backups.Subplan) and selected 'View History'. When the dialog displays
> the
> job is selected.
> Thanks,
> Terry
>
> "Ekrem Ã?nsoy" wrote:
>> Hi Terry,
>>
>> Could you tell me exactly what version and edition of SQL Server do you
>> use?
>> First two reasons I can think of:
>> 1- You did not select the checkbox just at the left side of the job name
>> from the Log File Viewer window.
>> 2- The Jobs have never worked (in your case, some of them worked, so this
>> must not be valid for you)
>> P.S.
>> You use SSMS, not SSMSE right?
>>
>> --
>> Ekrem Ã?nsoy
>>
>> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> news:BA438105-C888-45E8-8E7E-623852F82345@.microsoft.com...
>> > Hi Ekrem,
>> > Thank you for the reply. I checked out the history logs for the job
>> > and
>> > what is strange is that there are no entries? I right clicked on the
>> > job
>> > and
>> > selected 'View Histories' but no entries. Can you think of a reason
>> > why
>> > I'm
>> > not seeing any data?
>> > Thanks,
>> > Terry
>> >
>> > "Ekrem Ã?nsoy" wrote:
>> >
>> >> Hello Terry!
>> >>
>> >>
>> >> Check out Jobs' histories from the Jobs node under SQL Server Agent.
>> >> There,
>> >> you will find the one which doesn't work. It stops your maintenance
>> >> plan.
>> >>
>> >> Besides, you better setup your maintenance plan one by one, not an
>> >> "all
>> >> in
>> >> one" package.
>> >>
>> >>
>> >> --
>> >> Ekrem Ã?nsoy
>> >>
>> >>
>> >>
>> >> "Terry Wahl" <TerryWahl@.discussions.microsoft.com> wrote in message
>> >> news:F4444EBE-47FE-4C21-804E-71CB320513D7@.microsoft.com...
>> >> >I have a maintenance plan that is failing but I'm not sure why. Plan
>> >> > consists of:
>> >> > 1 Backup Database
>> >> > 2 Cleanup Task
>> >> > 3 Reorganize Index Task
>> >> > 4 Rebuild Index Task
>> >> > 5 Shrink Database Task
>> >> > 6 Update Statistics Task
>> >> >
>> >> > I have a notification message configured for errors. For some
>> >> > reason I
>> >> > receive two emails. One states success while the other states
>> >> > failure.
>> >> > On
>> >> > the success email I do not see any mention of steps 3,4 or 6 (from
>> >> > above).
>> >> > I
>> >> > cannot find any additional information in the logs. Below are the
>> >> > two
>> >> > emails. These emails are sent out back to back. Thanks in advance
>> >> > for
>> >> > your
>> >> > help.
>> >> >
>> >> > Terry
>> >> >
>> >> > ###################################################
>> >> > Success Email
>> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
>> >> > Report
>> >> > was generated on "MSSQL".
>> >> > Maintenance Plan: Database Backups
>> >> > Duration: 08:26:44
>> >> > Status: Succeeded.
>> >> > Details:
>> >> > Back Up Database (Full) (MSSQL)
>> >> > Task start: 9/28/2007 9:00 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > BAK Cleanup Task (MSSQL)
>> >> > Task start: 9/28/2007 9:07 PM.
>> >> > Task end: 9/28/2007 9:07 PM.
>> >> > Success
>> >> >
>> >> >
>> >> > Shrink Database Task (MSSQL)
>> >> > Task start: 9/29/2007 3:33 AM.
>> >> > Task end: 9/29/2007 3:35 AM.
>> >> > Success
>> >> >
>> >> >
>> >> > ###################################################
>> >> > Failed Email
>> >> > OB RUN: 'Database Backups.Subplan' was run on 9/28/2007 at 9:00:01
>> >> > PM
>> >> > DURATION: 8 hours, 26 minutes, 51 seconds
>> >> > STATUS: Failed
>> >> > MESSAGES: The job failed. The Job was invoked by Schedule 1
>> >> > (BackupDatabaseJob). The last step to run was step 1 (Subplan).
>> >> >
>> >>|||Ekrem Ã?nsoy wrote:
> Hi Terry!
>
> Can you check the Filter setting in the Log Viewer window? If it's set
> to some values then click Clear button in the Filter Settings window
> and click OK.
> Check if other logs are clear or do they have records from the Log
> File Viewer? (SQL Agent logs for example)
> If it is not your situation, then can you take a look at your Instance
> path? Check if log files are stored or not.
> For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> Note:
> I once faced this problem but in that situation the person who met
> this problem was using SSMSE and when he started using SSMS he started
> seeing logs.
>
Instead of viewing history on the SQL Agent job - view the history on
the maintenance plan. The history here will show each step in the
maintenance plan and should identify why the other steps are failing.
My guess as to why you are getting two messages is that the steps in the
maintenance plan are not sequenced. In the maintenance plan editor, you
need to drag the green arrow (go to next step on success) or the blue
arrow (go to next step on completion) and perform each step in the
maintenance plan in sequence. If your jobs are not sequenced - those
other steps are probably failing because they cannot obtain a lock or
are blocked by the other steps.
And finally, you do not need to perform all of those steps - and not in
the order you listed them. I generally run the following:
Integrity Checks (DBCC CHECKDB)
On Success: Rebuild/Reorganize Indexes
On Success: Backup Database
On Success: Maintenance Plan Cleanup Task
Depending upon the system, I may include a step to Update Statistics and
would include that step right before the backup. You should not shrink
the database as it will only grow again, especially when you are
rebuilding indexes. Make sure your database has been sized large enough
so that the rebuild index operation does not grow the database
(generally 20% free).
Jeff|||Hi Jeffrey,
Thanks for the reply. All the tasks are sequenced (blue connections). I
took a look at the history logs as you mentioned (thanks) but what is strange
is not all the task are contained in the log but yet the ones that are there
show success. I have turned on extended logging and I initially removed the
'Reorganize Index Task' and 'Rebuild Index Task' steps. When I did this
everything worked and all 4 entries are listed in the log. I later added
back the 'Reorganize Index Task' and the job failed. As mentioned above when
I view the log I do not see all the steps and the ones that are listed show
success. The 'Reorganize Index Task' is not listed. No failed steps are
listed. Strange deal.
Thanks,
Terry
"Jeffrey Williams" wrote:
> Ekrem Ã?nsoy wrote:
> > Hi Terry!
> >
> >
> > Can you check the Filter setting in the Log Viewer window? If it's set
> > to some values then click Clear button in the Filter Settings window
> > and click OK.
> >
> > Check if other logs are clear or do they have records from the Log
> > File Viewer? (SQL Agent logs for example)
> >
> > If it is not your situation, then can you take a look at your Instance
> > path? Check if log files are stored or not.
> > For Example: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
> >
> > Note:
> > I once faced this problem but in that situation the person who met
> > this problem was using SSMSE and when he started using SSMS he started
> > seeing logs.
> >
> Instead of viewing history on the SQL Agent job - view the history on
> the maintenance plan. The history here will show each step in the
> maintenance plan and should identify why the other steps are failing.
> My guess as to why you are getting two messages is that the steps in the
> maintenance plan are not sequenced. In the maintenance plan editor, you
> need to drag the green arrow (go to next step on success) or the blue
> arrow (go to next step on completion) and perform each step in the
> maintenance plan in sequence. If your jobs are not sequenced - those
> other steps are probably failing because they cannot obtain a lock or
> are blocked by the other steps.
> And finally, you do not need to perform all of those steps - and not in
> the order you listed them. I generally run the following:
> Integrity Checks (DBCC CHECKDB)
> On Success: Rebuild/Reorganize Indexes
> On Success: Backup Database
> On Success: Maintenance Plan Cleanup Task
> Depending upon the system, I may include a step to Update Statistics and
> would include that step right before the backup. You should not shrink
> the database as it will only grow again, especially when you are
> rebuilding indexes. Make sure your database has been sized large enough
> so that the rebuild index operation does not grow the database
> (generally 20% free).
> Jeff
>

Wednesday, March 28, 2012

Maintenance Plan does not delete old .bak files

I am using the maintenance cleanup task wizard to delete old .bak files older than 4 weeks, but it is not working.

I chose:

connection: local server connection

type: backup files

search files based on an extension

path to files

ext = bak

Delete files older than 4 weeks.

I run it and it never removes any of the files--what gives.What version on SQL you are using. IF you have installed SQL2005 SP2 before march 6th, then there is a know bug on cleanup task. You can download the latest SP2 build and install.
|||

This problem has been fixed in the latest Cumulative Update. Call MS Customer Service and ask for the SQL Server cumulative update. There is no charge for this service.

jkh

sql

maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

maintenance plan Cleanup Task

Hello all,

What is the latest after SP2 on the Maintenace Cleanup Task?

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2 with all the htfixes instaled? .

In other words can EXECUTE master.dbo.xp_delete_file 0,N'D:\backups',N'bak',N'2007-07-12T10:12:03',1 be used with bak and trn at the same time

Thanks in advanced

This has been fixed in the latest cumulative update for SQL Server. Contact Customer Support and they will direct you to the fix.

jkh

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

What is the issue?

What if I have already installed SP2 on my server?

What if I haven't yet installed SP2 on my server?

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Any help?

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>sql

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

What is the issue?

What if I have already installed SP2 on my server?

What if I haven't yet installed SP2 on my server?

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Any help?

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

What is the issue?

What if I have already installed SP2 on my server?

What if I haven't yet installed SP2 on my server?

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Any help?

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

What is the issue?

What if I have already installed SP2 on my server?

What if I haven't yet installed SP2 on my server?

Users, also please make a note of README for SP2:

The following error message is returned if you use a tool from a previous version to open an SP2 maintenance plan created with the maintenance plan designer:
"This maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL Server Integration Services to modify this maintenance plan from now on."
To resolve this issue, we recommend that you upgrade your tools to SQL Server 2005 SP2. We also recommend using the maintenance plan designer to modify the plan, rather than Integration Services as mentioned in the error message.

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

What is the issue?

What if I have already installed SP2 on my server?

What if I haven't yet installed SP2 on my server?

Users, also please make a note of README for SP2:

The following error message is returned if you use a tool from a previous version to open an SP2 maintenance plan created with the maintenance plan designer:
"This maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL Server Integration Services to modify this maintenance plan from now on."
To resolve this issue, we recommend that you upgrade your tools to SQL Server 2005 SP2. We also recommend using the maintenance plan designer to modify the plan, rather than Integration Services as mentioned in the error message.

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.
Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
|||Chris Wood (anonymous@.discussions.microsoft.com) writes:
> Pre SP2 the maintenance plans created SSIS packages. Is this still the
> behaviour in SP2?
I'm not into maintenance plans myself, but I believe that in SP2,
maintenance plans are part of the engine, and SSIS is not required.
There is a discussion on this in "What's New in SP2" in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Yes, SP2 Maintenance Plans still create SSIS packages. We do not plan on
changing this in the near future.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%232F$oTeWHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Ed,
> Pre SP2 the maintenance plans created SSIS packages. Is this still the
> behaviour in SP2?
> Thanks
> Chris
> "Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
> news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
>
|||Hello,
I used method:
public void AddAgentJob(ServerConnection LocalConnObj, string proxyName)
(class
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan)
but after installing SP2 for MS SQL Server, I can't find it.
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
sql

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.
Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
--
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>

Friday, March 23, 2012

Maintenance Cleanup Task……

SQL Server 2005 Standard Edition

Windows Server 2003.

I have created an ‘Backup Database Task’ to create the database backups and checked the option “Create a subdirectory for each database”.

When I add the “Maintenance Cleanup Task” I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the “Create a subdirectory for each database” option to create the backup files in one location.

Thank you,

Smith

Smith,
Once you create a Sub directory the backup files will be placed there...now in the clean up task there is an option called "Search folder and delete files based on an extension" you need to specify the sub directory folder in the above mentioned option........the clean up task will delete it and also you need to psecify the extension as "bak" to delete backup files.........refer the below link,
http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p2.aspx

|||

This article could be helpful for you

http://msdn2.microsoft.com/en-us/library/ms345177.aspx

|||

Deepak,

This link says...

"To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box."

I am not finding the "Includefirst-level subfolders" option to check.

How to check this option.

Thank you

-Smith

|||

Hi,

What Service Pack are you on? You should be getting that option when you Edit the Maintenance Plab Sub-task "Clean up Maintenance Task"

|||

select @.@.version

Thanks,

|||Try to install SQL 2005 SP2 critical update from
http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en

refer the below link,
http://support.microsoft.com/kb/933508 as there were many problems relating to maintenance plan which were fixed with critical update ! ! !
|||

Thank you for your suggestions in resolving this issue.

Since it is not a critical issue affecting the production I will wait for the SP3 :-)

Infact when I installed the SP2 I did download the latest copy available.

Thank you,

|||Well i don't think SP3 would be released for sql 2005 as sql 2008 is going to be released in Feb 2008 Smile

Maintenance Cleanup Task……

SQL Server 2005 Standard Edition

Windows Server 2003.

I have created an ‘Backup Database Task’ to create the database backups and checked the option “Create a subdirectory for each database”.

When I add the “Maintenance Cleanup Task” I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the “Create a subdirectory for each database” option to create the backup files in one location.

Thank you,

Smith

|||

This article could be helpful for you

http://msdn2.microsoft.com/en-us/library/ms345177.aspx

|||

Deepak,

This link says...

"To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box."

I am not finding the "Includefirst-level subfolders" option to check.

How to check this option.

Thank you

-Smith

|||

Hi,

What Service Pack are you on? You should be getting that option when you Edit the Maintenance Plab Sub-task "Clean up Maintenance Task"

|||

select @.@.version

Thanks,

Thank you for your suggestions in resolving this issue.

Since it is not a critical issue affecting the production I will wait for the SP3 :-)

Infact when I installed the SP2 I did download the latest copy available.

Thank you,

|||Well i don't think SP3 would be released for sql 2005 as sql 2008 is going to be released in Feb 2008 Smile

Maintenance Cleanup Task……

SQL Server 2005 Standard Edition

Windows Server 2003.

I have created an ‘Backup Database Task’ to create the database backups and checked the option “Create a subdirectory for each database”.

When I add the “Maintenance Cleanup Task” I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the “Create a subdirectory for each database” option to create the backup files in one location.

Thank you,

Smith

|||

This article could be helpful for you

http://msdn2.microsoft.com/en-us/library/ms345177.aspx

|||

Deepak,

This link says...

"To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box."

I am not finding the "Includefirst-level subfolders" option to check.

How to check this option.

Thank you

-Smith

|||

Hi,

What Service Pack are you on? You should be getting that option when you Edit the Maintenance Plab Sub-task "Clean up Maintenance Task"

|||

select @.@.version

Thanks,

Thank you for your suggestions in resolving this issue.

Since it is not a critical issue affecting the production I will wait for the SP3 :-)

Infact when I installed the SP2 I did download the latest copy available.

Thank you,

|||Well i don't think SP3 would be released for sql 2005 as sql 2008 is going to be released in Feb 2008 Smile

sql

Maintenance Cleanup Task……

SQL Server 2005 Standard Edition

Windows Server 2003.

I have created an ‘Backup Database Task’ to create the database backups and checked the option “Create a subdirectory for each database”.

When I add the “Maintenance Cleanup Task” I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the “Create a subdirectory for each database” option to create the backup files in one location.

Thank you,

Smith

|||

This article could be helpful for you

http://msdn2.microsoft.com/en-us/library/ms345177.aspx

|||

Deepak,

This link says...

"To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box."

I am not finding the "Includefirst-level subfolders" option to check.

How to check this option.

Thank you

-Smith

|||

Hi,

What Service Pack are you on? You should be getting that option when you Edit the Maintenance Plab Sub-task "Clean up Maintenance Task"

|||

select @.@.version

Thanks,

Thank you for your suggestions in resolving this issue.

Since it is not a critical issue affecting the production I will wait for the SP3 :-)

Infact when I installed the SP2 I did download the latest copy available.

Thank you,

|||Well i don't think SP3 would be released for sql 2005 as sql 2008 is going to be released in Feb 2008 Smile

Friday, March 30, 2012

Maintenance Plan Fails but I can't figure out why? No log informa

Wednesday, March 28, 2012

Maintenance Plan does not delete old .bak files

maintenance plan Cleanup Task

maintenance plan Cleanup Task

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Maintenance Plan Changes in SQL Server 2005 SP2

Friday, March 23, 2012

Maintenance Cleanup Task……

Maintenance Cleanup Task……

Maintenance Cleanup Task……

Maintenance Cleanup Task……

Lowering priority

Blog Archive

About Me