Maintenance Plan question... 1

[bmgmzp]

Does anybody have any advice/links for information on how to setup a maintenance plan for database(s) in SQL Server 2005?

Specifically, in the maintenance plan wizard.. Do I need to run the task labeled 'Check database integrity'? If and when do I need to run the shrink database task? etc.

I've found articles explaining what they are and what they do but not so many telling me yes I need to run it or no i dont. ;-)

 

[ptheriault]

You should run database integrity check at least weekly. They will find any torn pages.
As far as shrinking the database. That is not something that I schedule nor should you have to. As long as you are monitoring your database growth. Most DBA's manually manage the autogrow events so they don't impact IO during production hours. If you shrink your database past to small you find it will only autogrow again. Autogrow events will have a negative impact on server performance. So you are better off not shrinking your database. (unless you need to recover the disk space.)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[bmgmzp]

i can't find the truncate log option in the maintenance plan wizard or designer... once I backup the transaction log how do I truncate it?

 

[ptheriault]

The backup command will remove all of your commited transactions from the log. If you need to shrink your log to return the free space back to the OS use:

DBCC SHRINKFILE(<log_name>, truncateonly)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[bmgmzp]

So you're saying that shrinking the log is not something that I need to do all the time if i am backing up the logs regularly?

But as far a me finding an item in the toolbox in the maintenance plan designer i'm not going to find one... is that correct?

Thanks for your help to this point... my frustration level is slowly subsiding... ;-)

 

[ptheriault]

Correct.
You find that some maintanence tasks will cause the log to grow very large, such as a re-index. It only after these tasks that I shrink my log back down to normal size. (Normal size depends on how transactional your db is.)

- Paul

- If at first you don't succeed, find out if the loser gets anything.