Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

maintenance plans ran out of sequence

We have several maintenance plans that should run in this order each Saturday night
1.CheckDB
2.ShrinkDBCC
3.Alter index reorganize
4.Alter index rebuild
5.Backup

I changed the schedules so they wouldn't overlap. As a result, shrinkDBCC and Alter index reorganize did not run before Alter index rebuild and the backup ran. Our mdf is now 13GB whereas it used to be 11GB. The backup file is 11GB. We use a simple backup.

Has this caused a problem? What steps should I take now?
0
dwiseman3
Asked:
dwiseman3
  • 3
  • 2
2 Solutions
 
jogosCommented:
When backup is 11GB then you have 11GB of data and indexes. If your file is 13GB that means that there is 2GB free space for growth , seems reasonable.

Shrinking dbfiles means
- freeing space on disk to allocate it a few days later again .... fragmentation on disk.
- moving pages in your db around so your tables and indexes are scatered alover the db-file .... fragmentation in your database

With a database that size I think fragmentation is not something you are looking for.
0
 
sjurusCommented:
Hi,
First, do not use the DBCC Shrink, below the link to an article that explains it nicely:
http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx

Second, do the rebuilding and reorganizing indexes in one job,below the link to an article that explains it nicely
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Then you can do CHECKDB, below the link to the cool of procedure:
http://ola.hallengren.com/sql-server-integrity-check.html

And what is the frequency of backup depends on the requirements of business, below the link to the cool of procedure:
http://ola.hallengren.com/sql-server-backup.html
0
 
jogosCommented:
<<As a result, shrinkDBCC and Alter index reorganize did not run before Alter index rebuild and the backup ran. >>
Did they start and didn't end before next started? Or didn't the start at all?
In sql agent you can look at the job-history of the maintenance plan.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
dwiseman3Author Commented:
SHRINKDBCC and ALTER index reorganize did not start at all.
0
 
jogosCommented:
<<SHRINKDBCC and ALTER index reorganize did not start at all.>>
Checked the sql agent job? Is it enabled and next shedulled time is correct?
0
 
dwiseman3Author Commented:
yes
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now