Subplan in SQL Server 2005?  
Author Message
firoze





PostPosted: Wed Jun 20 06:40:01 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005?

When I use database maintenance wizard to create a maintenance plan. I find
that the job is named as "XXXX.subplan".

However, for those maintenance plan I create last December, the name is
"YYYY". In this way, I just open the them and save them. Then the jobs
become "YYYY.subplan".

Is it the change after applying SQL Server 2005 SP2 ? What is the main
reason for introducing subplan ?

Besides, is there any way to limit the size of log files for maintenance
plan ? I just use "Backup" and "Clean Up" only. Is it OK ?

Thanks

SQL Server275  
 
 
OmarAwwad





PostPosted: Wed Jun 20 06:40:01 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005? Hi,

"is there any way to limit the size of log files for maintenance
> plan ? I just use "Backup" and "Clean Up" only. Is it OK ?"

In order to manage the size of the log file you have the following options:

1- change the recovery option for database to Simple if you are not include
the log file in your backup startegy.
2-Shrinking the log file. (http://support.microsoft.com/kb/272318/)

Thanks
Omar







> When I use database maintenance wizard to create a maintenance plan. I find
> that the job is named as "XXXX.subplan".
>
> However, for those maintenance plan I create last December, the name is
> "YYYY". In this way, I just open the them and save them. Then the jobs
> become "YYYY.subplan".
>
> Is it the change after applying SQL Server 2005 SP2 ? What is the main
> reason for introducing subplan ?
>
> Besides, is there any way to limit the size of log files for maintenance
> plan ? I just use "Backup" and "Clean Up" only. Is it OK ?
>
> Thanks
>
>
>
>
 
 
jbellnewsposts





PostPosted: Wed Jun 20 06:44:01 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005? Hi Peter



> When I use database maintenance wizard to create a maintenance plan. I find
> that the job is named as "XXXX.subplan".
>
> However, for those maintenance plan I create last December, the name is
> "YYYY". In this way, I just open the them and save them. Then the jobs
> become "YYYY.subplan".
>
> Is it the change after applying SQL Server 2005 SP2 ? What is the main
> reason for introducing subplan ?
From
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm :

"Multiple schedules. The Maintenance Plan Designer supports multiple
subplans for a maintenance plan. Each subplan can have an independent
schedule for maintenance tasks"
>
> Besides, is there any way to limit the size of log files for maintenance
> plan ? I just use "Backup" and "Clean Up" only. Is it OK ?
>
> Thanks
>
Your cleanup task can remove old maintanance task report files.

Without rebuilding/reorganizing indexes you may start to get performance
problems if the indexes become fragmented.

To limit the growth of the database log files you may want to implement as
task that periodically backs up the log file.

HTH

John
 
 
YPD





PostPosted: Wed Jun 20 08:08:43 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005? Peter,

I've see the same behavior with SSIS maint plans. If I remeber it right, it
was since sp1 that subplans started to be introduced with a purpose to
schedule various subplans within the same plan. If you want different tasks
to act at different schedules, you can't unless you put them into different
subplans since there isn't a scheduling option at the task level.

In terms of limiting the size of log files, would you confirm that the clean
up task actually work on those log files?



> When I use database maintenance wizard to create a maintenance plan. I
> find that the job is named as "XXXX.subplan".
>
> However, for those maintenance plan I create last December, the name is
> "YYYY". In this way, I just open the them and save them. Then the jobs
> become "YYYY.subplan".
>
> Is it the change after applying SQL Server 2005 SP2 ? What is the main
> reason for introducing subplan ?
>
> Besides, is there any way to limit the size of log files for maintenance
> plan ? I just use "Backup" and "Clean Up" only. Is it OK ?
>
> Thanks
>
>
>


 
 
Peter





PostPosted: Thu Jun 21 05:45:37 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005? Dear YPD,

Thank you for your info.

Do you think that subplan is created only for maintenance plan ? This is
because I find that on another SQL 2005 Server, the vendor has created some
jobs with stored procedures that it does not contain the word "Subplan".

On the other hand, the job for maintenance plan I created yesterday failed.
It is a system database backup with clean up task - remove any backup with 3
days old. However, system databases are backed up successfully. I guess it
may be due to there is only 1 day backup and thus it fails. Is it sensible
?

Peter



> Peter,
>
> I've see the same behavior with SSIS maint plans. If I remeber it right,
> it was since sp1 that subplans started to be introduced with a purpose to
> schedule various subplans within the same plan. If you want different
> tasks to act at different schedules, you can't unless you put them into
> different subplans since there isn't a scheduling option at the task
> level.
>
> In terms of limiting the size of log files, would you confirm that the
> clean up task actually work on those log files?
>


>> When I use database maintenance wizard to create a maintenance plan. I
>> find that the job is named as "XXXX.subplan".
>>
>> However, for those maintenance plan I create last December, the name is
>> "YYYY". In this way, I just open the them and save them. Then the jobs
>> become "YYYY.subplan".
>>
>> Is it the change after applying SQL Server 2005 SP2 ? What is the main
>> reason for introducing subplan ?
>>
>> Besides, is there any way to limit the size of log files for maintenance
>> plan ? I just use "Backup" and "Clean Up" only. Is it OK ?
>>
>> Thanks
>>
>>
>>
>
>


 
 
jbellnewsposts





PostPosted: Sat Jun 23 14:49:00 CDT 2007 Top

SQL Server >> Subplan in SQL Server 2005? Hi Peter



> Dear YPD,
>
> Thank you for your info.
>
> Do you think that subplan is created only for maintenance plan ? This is
> because I find that on another SQL 2005 Server, the vendor has created some
> jobs with stored procedures that it does not contain the word "Subplan".

The names of the jobs and schedules can be anything that you specify
although having subplan in them may help you identify them more easily.

>
> On the other hand, the job for maintenance plan I created yesterday failed.
> It is a system database backup with clean up task - remove any backup with 3
> days old. However, system databases are backed up successfully. I guess it
> may be due to there is only 1 day backup and thus it fails. Is it sensible
> ?
Check the log file for the job to see if there is more information regarding
why this failed. If this continues to fail you could run the plan yourself
using dtexec and change the /REPORTING option to V (for verbose)

>
> Peter
>

John