Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

database maintenance task sql

Posted on 2015-01-26
2
Medium Priority
?
213 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
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 53

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

572 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