SQL SERVER – Automation Process Good or Ugly

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)

About these ads

2 thoughts on “SQL SERVER – Automation Process Good or Ugly

  1. Automation dont have ugly side, only bad thing is overhead on the resources that some processes (agent/scheduler) keeps on running to trigger the task/jobs on its schedule…:).

    I think, when a process is fixed and need to be run periodically… certainly Automation is best. It saves lots of manual efforts and time as well. But need to be implemented carefully, otherwise it could come up looking UGLY. (like the ones that you listed in UGLY section).

    -Sajid

  2. if error to excell some data mantnace in iso9000 and all those topics to fussion with enviroments of storage in private audittting red error to budget a life cicle.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s