Solved

database maintenance task sql

Posted on 2015-01-26
2
164 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 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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now