Microsoft SQL Server comes with a boatload of additional components. One component is the SQL Agent. 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 SQL Agent.
1. 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 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 datetime, regardless of log, as you enable viewing by clicking in the corresponding checkbox. This can be valuable when trying to troubleshoot oddball issues that affect things both internal and external to SQL Server.
3. Alerts
Not many people use this feature of SQL Agent, mostly due to the rise of third-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 believe in protecting myself from failure in a variety of ways. No matter 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 failure than never at all.
4. Multi-server administration
Another feature that has been unknown to many for years is the concept of multi-server administration. 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. 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.
