Solved

database maintenance task sql

Posted on 2015-01-26
2
187 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 250 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 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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