x

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

vijay 2 gravatar image

vijay 2
236 20 21 23

What are trying to achieve?

Backups, indexes rebuild, checkdb, logs clean ups etc etc?
Aug 12, 2010 at 09:22 AM sp_lock

Yes,

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][2] on how to use the scripts.

[2]: http://www.simple-talk.com/sql/database-administration/automate-and-improve-your-database-maintenance-using-ola-hallengrens-free-script/
more ▼

answered Aug 12, 2010 at 10:48 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(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][1])

[1]: 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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

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.

Thanks,
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x33

asked: Aug 12, 2010 at 08:35 AM

Seen: 1452 times

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