This blog post is written in response to T-SQL Tuesday hosted by SQL Server Insane Asylum.

The idea of this post really caught my attention. Automation – something getting itself done after the initial programming, is my understanding of the subject. The very next thought was – is it good or evil? The reality is there is no right answer. However, what if we quickly note a few things, then I would like to request your help to complete this post.

We will start with the positive parts in SQL Server where automation happens.

The Good

If I start thinking of SQL Server and Automation the very first thing that comes to my mind is SQL Agent, which runs various jobs. Once I configure any task or job, it runs fine (till something goes wrong!). Well, automation has its own advantages. We all have used SQL Agent for so many things – backup, various validation jobs, maintenance jobs and numerous other things.

What other kinds of automation tasks do you run in your database server?

The Ugly

This part is very interesting, because it can get really ugly(!). During my career I have found so many bad automation agent jobs.

• Client had an agent job where he was dropping the clean buffers every hour
• Client using database mail to send regular emails instead of necessary alert related emails
• The best one – A client used new Missing Index and Unused Index scripts in SQL Agent Job to follow suggestions 100%. Believe me, I have never seen such a badly performing and hard to optimize database. (I ended up dropping all non-clustered indexes on the development server and ran production workload on the development server again, then configured with optimal indexes).
• Shrinking database is performance killer. It should never be automated. SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
• The one I hate the most is AutoShrink Database. It has given me hard time in my career quite a few times. SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server

Automation is necessary but common sense is a must when creating automation.

Reference: Pinal Dave (http://blog.SQLAuthority.com)