Automation is Power

Recently, I had someone looking over my shoulder as I was setting up a new server. I tend to welcome opportunities like that because I’m all about learning and teaching, and it’s always a positive thing to have an interested audience. :-) One of the many things my friend was surprised by is the number of jobs I have on a default installation of SQL Server. In short, once I’m done with an installation, before I start adding user databases, I create anywhere between 10 and 20 jobs, depending on the purpose of the server. Some of these are just maintenance tasks, like backing up or running CHECKDB on the system databases. Others are monitoring jobs, like logging the output of Adam Machanic’s (b | tsp_WhoIsActive every 5 minutes, or the output of a few of Glenn Berry’s (b | tdiagnostic scripts every 15 minutes. Not to mention keeping running logs of wait statistics, job history analytics, msdb history purging, cycling the error logs… you name it, if it’s a repeatable task, I have a job for it in my post-installation scripts.

Give that server a few months, and there are a whole host of other jobs that enter the picture. Many of my servers have hundreds of jobs on them. Outside of the user or vendor required jobs, or even the basic backup / CHECKDB / index & stats maintenance trifecta, I’ll have jobs in place that do automated restore tests, monitor for specific conditions, etc… In fact, any time someone comes to me with something that *can* be automated, I create a script and a job for it. I do this because, of all the one-time requests I’ve ever received from my users, almost none of them were actually one-time requests. They almost always crop up again. When they do, it’s a simple matter of opening up SSMS, right-clicking on the job, and selecting “Start job at step…”. Alternately, I could even schedule the task for a specific time, so I don’t even have to be there for it.

What kinds of tasks do you automate?

Thanks for reading.


Please Comment

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s