SQL Server Performance Forum – Threads Archive

Maintenance Plan Issues 2005

Maintenance Plan When I tried to create Maintenance Plan [MP] for full DB Backup and Hourly transaction log, I was not able to do that in one plan. I have tried both design and wizard option. The reason for that is in 2005 it will not allow you to have 2 schedules i.e. one for full backup say every night and other for transaction logs every hour. 2005 MP only create one job for plan. In sql2000 in one plan you can specify this 2 task and create 2 schedules, which in turn will create 2 separate jobs one for full db backup and one for transaction log. In addition to this 2005 maintenance plan also presents another issue when it comes to deleting old backup files after specified days. If you specify to create separate dir
in plan, then deletion module will not check sub-directories and you are left without removing files. Cleanup Task of MP uses EXECUTE master.dbo.xp_delete_file this command
is non-documented and you hardly find any references to this. Last thing is plan is not intuitive because to save the plan you need to jump back to main menu on top File Ã Save All option, which is not comfortable anyway. I am familiar to .net and frequently writes application in C# and I understand this look and feel of Visual Studio integration, but even than this is not straight forward as I too have lost changes to MP moving in management console of sql2005. I have also attended Microsoft Launch event in Atlanta on 29th Nov and talked to the experts over there, they definitely were very helpful but still I am not convinced on Microsoft#%92s standard argument that like other features MP is different in 2005. I too hear another argument that there are different ways of getting things accomplished. In this case writing job with T-Sql and not using MP at all. I know that and this is what we all do every now and then as DBA or developer. Question here is Maintenance plan is part of Sql server 2005 and it provides option for all types of Backup then it is supposed to work right. I will highly appreciate time and thoughts from all of you friends here. I might be missing something on my learning curve to this new product or something is not working properly in new product. Kindest Regards,
Sameer Raval [DBA-SQL Server]

Sameer, Can’t you just include another T-SQL task in the same MP, and schedule to backup every hour?

]]>