?
Solved

database maintenance task sql

Posted on 2015-01-26
2
Medium Priority
?
193 Views
Last Modified: 2015-01-27
Currently I have 6 database maintenance tasks firing off in one maintenance plan.
this plan runs once a week and has the following items

1) Check Database Integrity - all databases
2) Reorganize Index - all databases
3) Rebuild Index - all databases
4) Update Statistics - all databases
5) History Cleanup - all databases - keeping one month
6) Maintenance Cleanup Task - deletes bak files and trn files

Please advise if something is very wrong with doing all of these in one plan once a week.
0
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 1000 total points
ID: 40571841
Does your maintenance plan include backups?
I wouldn't say there was anything wrong with doing this weekly but then it all depends how often it *needs* to run.
Are the indexes fragmented? Are your stats out of date?
This all depends on how much the data is used/changed.

That said, I wouldn't use maintenance plans. I haven't for quite some time as you don't get enough control when using them.
Your MP reorganises AND rebuilds indexes. Why? A rebuild is going to cancel out the reorganisation.
Also, what if your indexes are only 5% fragmented? In this case then neither a rebuild or reorganisation is really needed and will lead to longer maintenance times and large log files.

This is the kind of granular control you don't get with MPs.
I use the scripts at the URL below and have been happily doing so for a long time.
You run the scripts, change a couple of (well documented) parameters and it runs as a scheduled task at a schedule chosen by you.

https://ola.hallengren.com/
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40572208
2) Reorganize Index - all databases
 3) Rebuild Index - all databases
 4) Update Statistics - all databases
You don't need to Reorganize indexes and then Rebuild them and Update Statistics since Rebuild Index it's already all inclusive. What you can do is a weekly rebuild and daily reorganize.

As QPR said, these tasks don't take in consideration the fragmentation level so you may end running tasks without need. I also like Ola's script. It's very complete and it works perfectly.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question