Here is how a run my SQL Server maintenance plans and do maintenance.
i Have updated this post a few time's as i read some new onions...
i Set it for once a week...
i Choose the following tasks:
Update: SharePoint checks the index, it calls the stored procedure "proc_DefragmentIndices" so you can leave that step if that storeproc is under the DB, else you need to run a rebuild index.
Also important to regularly check the DB integrity
Remember to not run A/V scans on your SQL server files.
Set your "Default index fill factor" to 80(Under DB Settings)
Then a change the order, have seen people do the backups last but a want a backup before anything gets corrupted.
If you have any comment on the tasks please post a comment, this is the way i do it...Probably a few opinions on this.
On the next page choose you backup location and set the other settings
Same for Transaction log backups page.
On Backup integrity there is not much to set but choose your db's
On index rebuild again choose your db's and select "Change free space per percentage to:" 80%
Update stats again choose your db's
Clean up history and the clean up tasks is handy to help you not letting you backups filling up your drives.
Dont include a Shrink in your maint plan.
It is also debatable if you want to include rebuild index and update statistics as SharePoint has got a timer job for this.
i Am sticking to Index rebuild...Looks like there are ways to check how much it is fragmented and then decide if rebuild or re-index. i Still think rebuild cant hurt so that is why im sticking to it but that is my 2cents
Then to shrink your log files:
Go to the database with a big log file, properties and options. There you will find the Recovery Model. Set this to simple.
Right click on your DB again, Tasks, Shrink and Files
Here you can change the File Type to Log and click ok.
After this i run "DBCC CHECKDB" just to make sure all is still healthy.
Also look at the Microsoft Whitepaper on SharePoint SQL Maintenance.
Very helpfull! and some more details in there which i will probably add as i read it more in detail