Avoid using the SQL Agent?

Yeah, why wouldn't you use such a great tool as the SQL Agent?

Well, first of all I did not say you should not use it. Just to avoid it. Better said, use the SQL Agent for what it is intended for: maintaining your SQL Server.

Sometimes I see the SQL Agent being used to perform all kinds of tasks that an application should do. Those things should preferably be handled by the application, not the SQL Agent.

Or it is used for tasks that could (and should) be done using Windows Task Manager. Also in that case, don't use the SQL Agent when it should be done by the Windows Task Manager.

Yeah well, but there are some cases...

Yes, sometimes you just need the SQL Agent for something other than just SQL Server Maintenance and there is no other option available. In those cases, go ahead and do it.

But wait...

If you do this, be sure to make a regular backup of the MSDB System Database. The reason for that is that that is where your Jobs and their History is stored. If for some reason your server gets down, you will definitely need that one to restore all your precious jobs.

Since SQL Agent Jobs do their job unattended, they are easliy taken for granted. And since a lot of people are not aware that the Jobs and their History are stored in the MSDB, a lot of times people forget to backup that one.

Now you know so check if your MSDB is part of the maintenance plan.

And when you do use the SQL Agent...

Here are some best practices:

Don't put your code in the Job

In a SQL Agent Job you can tell which SQL Code should be executed. So you might be tempted to put your Code in there. Like this:

But it is a much better idea to put Database specific Code in the Database itself as a Stored Procedure. Then run the Stored Procedure using the SQL Agent:

That way, even if you don't have a backup of your MSDB Database in a case of emergency, you still have your code.

Set up Notifications when the Job Fails

If a job fails, it might go by unnoticed. Maybe the backup job failed because of a lack of disk space. You will only know when you need the backup. In other words, you will know it when it is too late.

So always set up notifications for critical jobs. That way you will get an email when something goes wrong. This video explains how to do just that.

Schedule at irregular times

Although it looks clean and organized to start jobs exactly on the hour, avoid starting all your jobs at the same time. If you use irregular times like 12:09 or 02:07, you can easily work your way around it. In general it is good practice to get a clear picture of what is happening and at what time. So be sure to document your jobs well.

Use Job Groups

To organize your Jobs, you can use the Job Groups. SQL Server comes with build in Job Groups that are quite common. But you can add your own Custom Job Groups as well.

That way you can organize them by Customer or Database. Or if it is a dedicated server for 1 Customer and just 1 Database, I usually organize them by purpose.

And the rest is History!

Yes, do not forget about the History. Especially if you have Jobs that run every few minutes every day of the week. Your History might get quite big. So check the Settings under the SQL Agent Properties and make sure you don't keep everything for ever and eternity.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!