Microsoft SQL Server comes with a boatload of additional components. One component is the SQL Agent service. The purpose of the SQL Agent is to serve as a job scheduler. Many experienced DBAs use jobs running inside the SQL Agent to perform routine tasks such as backups, updating statistics, and rebuilding indexes as needed.
While the presence of SQL Agent may be known to many, I always find people to be surprised by the existence of one or more items on this list.
So here you go, five things that you didn’t know about the SQL Agent.
1. SQL Agent Specific Performance Objects
Performance Monitor (aka, PerfMon) has a wealth of metrics that are likely familiar to any DBA. Items such as Page Life Expectancy, Buffer Cache Hit Ratio, and CPU Utilization are some of the common counters collected by any experienced administrator.
What is not as well known about the counters installed right alongside the usual suspects are the counters specific for SQL Agent.
Yep, these exist:
You can get all the details on these objects over at http://technet.microsoft.com/en-us/library/ms190382.aspx.
Oddly enough, there is no DMV similar to sys.dm_os_performance_counters available to query for these details on the SQL Agent. You would need to write a query against the msdb database in order to collect the information that is readily available from these counters. Depending upon your needs, these counters may be preferred over querying the msdb database directly.
2. SQL Agent Log file
Most everyone knows that there is an error log for SQL Server. Not everyone is aware that a log also exists for SQL Agent. You can find it inside of SQL Server Management Studio:
Double clicking on one of the logs displayed inside of SSMS will open up the Log File Viewer, and from there you can see all of the logs available for you to browse:
What I like about this viewer is that it automatically sorts all events by date, regardless of log, as you enable viewing by clicking specific logs in the corresponding checkboxes. This can be valuable when trying to troubleshoot oddball issues that affect things both internal and external to SQL Server.
3. SQL Agent Alerts
Not many people are using this feature of SQL Agent, mostly due to the rise of 3rd party products over the past 15 years that allow for centralized alerting of your SQL Server. But the native alerting feature inside of SQL Server is fairly robust in what it can offer.
Need to be alerted if there is database corruption? What about if there is a T-SQL syntax error? How about for a hardware error? All of those things are possible out of the box with SQL Server:
As a DBA I know that my first priority must be my ability to recover data. Therefore, I believe in protecting myself from failure in a variety of ways. No matter what is the preferred alerting tool for the enterprise I always like to configure some alerts within SQL Agent as a failsafe for items such as database corruption. Better to be alerted more than once for a significant failure than never at all.
4. SQL Agent Multiserver Administration
Another feature that has been unknown to many DBAs for years is the concept of multi-server administration. That’s right, you can configure one of your SQL Server instances to act as a centralized system to control others.
It’s easy to launch the wizard with a simple right-click:
You can configure one server to be ‘Master’, and additional servers to be the ‘Target’. I always advise using a non-production (or dedicated) server to serve as Master, so as to not interfere with any production workloads.
The advantage here is that you can create one job on the Master server and have it executed on all of the Target servers. This can make your administration efforts much less complex.
5. SQL Agent Auto Restart
You can configure SQL Agent to auto restart both itself and SQL Server in case either service quits unexpectedly. Just right-click on the SQL Agent service inside of SSMS:
And now should SQL Agent service stop unexpectedly it will attempt to restart itself. Not a bad thing to have handy, especially if you are relying on jobs to be executed!
There you go, five things you may not have known about SQL Agent inside of SQL Server.
I have to disagree about multi-server administration being a single point of failure. If your master server fails, the target servers will keep following their instructions until you defect and re-enroll in a new master server. We use this pretty heavily in my environment.
Thanks Mike! That’s not how I remember it working. What version of SQL are you using now? I’m wondering if things may have changed.
Part of my “single point of failure” comment was that I was thinking about how an error in one place can end up being deployed in many places, but that’s no different than what we already do daily, right?
I’m going to edit that paragraph from this post for now, and probably use it for a follow up post.
Thanks again! BTW, my daughter is watching me write this and says “hello”.
If you have an error in a script, it will get deployed to every server. In that, you’re absolutely correct.
The other challenge of the MSX/TSX relationship is that setting a schedule for an IO intensive job can bring your SAN to its knees. For some jobs, we deploy them without a schedule so we get the benefit of keeping the job code consistent, and then we deploy a local job that calls the master job. This gives us the flexibility to schedule that job when we want.
When you manage hundreds of instances, MultiServer Administration can be a huge help by making sure that every server has the same version of your backup script, index maintenance script, etc. If I ever write a book, it will be around this.
Yep. We had to stagger our backup job times as a result of IO pressure as well. I used OpsMgr to keep my job code in synch, too.
I was introduced to multi-server administration in my first real SQL
admin job, but it’s been a hard sell in subsequent environments. Thanks
for the recommendation of using a local job to run a master job.
Scheduling has sometimes been a headache.
I look forward to your book on multi-server administration! It’s been a well-kept secret for far too long.