Do we really need maintenance plans

Question

Do we really need maintenance plans

v-lalakh

Grasshopper

Points: 24
More actions
February 2, 2015 at 1:40 am

#295051

Hello All,
While reviewing current maintenance plans a question popped up, do we really need maintenance plans ? As per my understanding we can perform all maintenance plans like taking and maintaining backups, index maintenance etc by creating scripts and scheduling with SQL jobs. Creating script also provide more scope for customization.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Maintenance plans are there so that you do actually maintain your databases, which must be done. But, as you get more sophisticated, yes, you're probably going to stop using maintenance plans and start writing scripts for everything. There is nothing being done in the maintenance plans that you couldn't do on your own. It's just easy with the maintenance plans.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

v-lalakh Grasshopper Points: 24 More actions · Answer 2

Thanks for reply Grant.

I have read many articles of yours and follow your blogs.

One more advantage of not using maintenance plans is that we can save memory consumed by integration services.

Thanks again for reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Like many people I started out using maintenance plans then began writing my own scripts. Like Grant said, there's nothing that a maintenance plan does that you can't script out. I like scripting out my maintenance work because I am less limited.

Here's where I still use maintenance plans: when

(1) I'm delegating work to someone else

and

(2) The person whom I'm delegating to is not great at scripting or I need it done real quick.

Maintenance Plans are the fast down and dirty way to get it done fast when you don't have time to script it

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQLBill SSC Guru Points: 51440 More actions · Answer 4

As I see it, maintenance plans are like a GUI....they get the job done, but you are allowing SQL Server to create the scripts and figure out how to do the work. You can do the same thing and have more control by writing the scripts yourself. It's similar to using GUIs/Wizards to do other functions on a database. For example: you could back up a database by right clicking on it in SSMS and selecting Tasks>Backup. But you are better off writing the BACKUP DATABASE script yourself as you can have more control over what happens.

-SQLBill

aprathour.89 Default port Points: 1495 More actions · Answer 5

Maintenance plans are too easy to schedule Backup,Restore and Index rebuild activities and its great for beginners DBAs ,who don't have too much experience with writing scripts also it saves time.

With writing scripts you have more control and can customize your maintenance activities as you wish but it requires good scripting knowledge.

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.