Maintenance plan Scheduling?

Hello All,

Currently we are using SQL 2008 for our development databases. We are planning to schedule maintance plan. so, please suggest the sequence of the maintance tasks.

Thanks in advance, Veejay.

more ▼

asked Aug 12, 2010 at 08:35 AM in Default

avatar image

vijay 2
236 21 21 26

What are trying to achieve?

Backups, indexes rebuild, checkdb, logs clean ups etc etc?

Aug 12, 2010 at 09:22 AM sp_lock


We are trying to rebuild the indexes,integrity check,update stats,Shrinking the database.

Aug 12, 2010 at 09:28 AM vijay 2
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Dont shrink the database, If your databases are in Full recovery mode make sure u take log backup along with full backup. If you dont want point in time recovery change the recovery model to simple, that way you dont need to maintain the logs. you can download and use this free super awsome script by Ola Hallengren for all your maintenance needs: SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

Here is an article on how to use the scripts.

more ▼

answered Aug 12, 2010 at 10:48 AM

avatar image

4.9k 33 39 43

(comments are locked)
10|1200 characters needed characters left

DaniSQL has a good answer, and in particular I agree you should not automatically shrink the databases. Personally, I do database shrinking only if there is an actual need for the space, I do it manually, and I then start requesting additional drives.

For very basic maintenance you can use the wizard to build a maintenance plan (I am assuming you are using SQL Server 2005 or later since you didn't specify). You loose a lot of control that way and that will cause problems in complex environments, but for a fairly simple environment with a true dedicated maintenance window, you can use the wizard and expect it take care of your needs for a while.

For more complex environments, well entire books have been written about that. In particular, you may want to look at Brad's Sure Guide to SQL Server Maintenance Plans (DBA Handbooks) (http://www.amazon.com/Brads-Guide-Server-Maintenance-Handbooks/dp/1906434344/ref=sr_1_4?ie=UTF8&s=books&qid=1281648942&sr=8-4)

more ▼

answered Aug 12, 2010 at 02:46 PM

avatar image

15.6k 22 57 38

He can also download the free copy of [Brad's Sure Guide to SQL Server Maintenance Plans][1] from simple-talk.com [1]: http://www.simple-talk.com/books/sql-books/brads-sure-guide-to-sql-server-maintenance-plans/

Aug 13, 2010 at 06:30 AM DaniSQL

I didn't realize there was a free e-book version. Thanks.

Aug 13, 2010 at 09:56 AM TimothyAWiseman

Hi Dani/Timothy,

Many thanks for Your updates, Now we are using SQL 2008 and already i Created aplan like 1. Rebuild Indexes---Weekly 2. Update Statistics---Weekly 3. Database Integrity Check---Weekly 4. Shrinking---Once the above tasks completed i shrink after 1 day.

And also i am taking every two hours Transactional Backup and Daily once Full Backup.

If anything needs to be change Please suggest.


Aug 18, 2010 at 08:04 AM vijay 2

It is hard to comment on your plan without knowing the details of your situation, but that falls within all the general guidelines and rules of thumb. If you are satisfied with the answer, please mark DaniSQL's answer as accepted.

Aug 18, 2010 at 09:37 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 12, 2010 at 08:35 AM

Seen: 1680 times

Last Updated: Aug 12, 2010 at 08:35 AM

Copyright 2018 Redgate Software. Privacy Policy