SQL SERVER – How to Get SQL Server Agent Properties?

During Comprehensive Database Performance Health Check we always go over various SQL Server properties. Lots of people do not know that there are quite a few SQL Server settings which can help us improve our SQL Server performance many folds. While I was discussing SQL Server properties, someone asked me if there are similar settings for SQL Server Agent available which users can tune.

This is indeed a very interesting question as there are lots of SQL Server Agent properties but not many people are aware of the same. There are two different ways to check various properties of SQL Server Agent.

Method 1: Right click on SQL Server Agent node and explore various properties.

Method 2: With T-SQL

EXEC msdb.dbo.sp_get_sqlagent_properties

Here is the T-SQL command which you can run on SQL Server Management Studio new query windows to get all the properties of Agent.

Please note if your Agent is turned off or the services are not installed, you will get empty resultset for this query.

Let me know if you have ever changed any of the SQL Server Agent’s properties and if the answer is yes, I would like to know what was the business reason behind it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

First appeared on SQL SERVER – How to Get SQL Server Agent Properties?

from SQL Authority with Pinal Dave https://ift.tt/2FuBJep