Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Maintenance Plan question... 1

Status
Not open for further replies.

bmgmzp

Programmer
Aug 18, 2006
84
US
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. ;-)
 
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
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
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?
 
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
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
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... ;-)
 
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
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top