Maintenance Plan setup

[MasterRacker]

I have a SQL 2005 server with a half dozen databases on it. They're all backends for COTS applications that were installed in a "turn it on and let it run" mode. I have no maintenance going on at all at the moment. Backups are accomplished via Backup Exec with the SQL agent - we don't do anything through SQL itself.

I'm seeing a couple of DBs now that have enormous log files that need to be shrunk. What should I be looking at from a best practices perspective as far as setting up a basic template for maintenance plans?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[HestonJames]

Hello Jeff,

I had myself a very similar situation to your recently so know what you're up against. MS actually give you some really nice tools for this, mainly the maintenance plan Wizard.

It all depends on what you want to achieve the the plan, I have a collection of about 5 DB's on my server and I have a single task which shrinks, backs up, rebuilds stats and indexes and cleans up old backup files.

In SSMS expand the 'management' tree in the object explorer in the left hand pain, then right-click the 'Maintenance Plan' object and you can chose 'Maintenance Plan Wizard', this will guide you step by step choosing which tasks you wan't to run on each database and at what time.

Once this is done the task will be given to the Agent to execute on a regular basis.

Two suggestions:

1) Schedule the tasks for a quiet period, usually the early hours of the morning or whenever the load is lowest or not likely to cause problems to your users.

2) Configure database mail and an Agent operator who can be emailed the result of the scheduled task, this helps you keep track of them things run correctly and when they go wrong.

Ask away if you have any other questions.

Heston