Agent Properties

SQL Server Agent has many operational properties that can be critical for troubleshooting various issues. Many of these properties are surfaced via GUI properties pages, and as such can be hard to compare across many SQL Server instances, without the process being highly error-prone.

SQL Server Agent Properties - General

The script below compiles the many sources of properties that affect SQL Server Agent into a single output table, which can be used to compare settings across multiple instances.

The output looks like:

╔═════════════════════════════╦═══════════════════════════════════════════════════════════════════╗
║          ItemName           ║                             ItemValue                             ║
╠═════════════════════════════╬═══════════════════════════════════════════════════════════════════╣
║ AgentDomainGroup            ║ NT SERVICE\SQLAgent$INSTANCE                                      ║
║ AgentLogLevel               ║ 7                                                                 ║
║ AgentMailType               ║ 1                                                                 ║
║ AgentShutdownWaitTime       ║ 15                                                                ║
║ BuildClrVersionString       ║ v4.0.30319                                                        ║
║ Collation                   ║ SQL_Latin1_General_CP1_CI_AS                                      ║
║ CollationID                 ║ 872468488                                                         ║
║ ComparisonStyle             ║ 196609                                                            ║
║ ComputerNamePhysicalNetBIOS ║ SERVER                                                            ║
║ DatabaseMailProfile         ║ DBA                                                               ║
║ Edition                     ║ Enterprise Edition (64-bit)                                       ║
║ EngineEdition               ║ 3                                                                 ║
║ ErrorLogFile                ║ G:\Database\Data\INSTANCE\MSSQL13.INSTANCE\MSSQL\Log\SQLAGENT.OUT ║
║ ErrorLogPath                ║ F:\Database\Errorlogs\INSTANCE                                    ║
║ HostLoginName               ║                                                                   ║
║ IdleCpuDuration             ║ 600                                                               ║
║ IdleCpuPercentage           ║ 10                                                                ║
║ IsCaseSensitive             ║ 0                                                                 ║
║ IsClustered                 ║ 0                                                                 ║
║ IsCpuPollingEnabled         ║ 1                                                                 ║
║ IsFullTextInstalled         ║ 1                                                                 ║
║ IsSingleUser                ║ 0                                                                 ║
║ JobServerType               ║ 1                                                                 ║
║ LocalHostAlias              ║                                                                   ║
║ LoginTimeout                ║ 30                                                                ║
║ MasterDBLogPath             ║ F:\Database\Data\INSTANCE\master\log                              ║
║ MasterDBPath                ║ G:\Database\Data\INSTANCE\master\system                           ║
║ MaximumHistoryRows          ║ 100000                                                            ║
║ MaximumJobHistoryRows       ║ 10000                                                             ║
║ MaxPrecision                ║ 38                                                                ║
║ MsxServerName               ║                                                                   ║
║ Name                        ║ SERVER\INSTANCE                                                   ║
║ NetName                     ║ SERVER                                                            ║
║ NetSendRecipient            ║                                                                   ║
║ ProductLevel                ║ SP2                                                               ║
║ ReplaceAlertTokensEnabled   ║ 1                                                                 ║
║ ResourceLastUpdateDateTime  ║ 2018-07-20 23:59:05.100                                           ║
║ ResourceVersionString       ║ 13.00.5081                                                        ║
║ RootDirectory               ║ D:\Program Files\MS SQL Server\MSSQL13.INSTANCE\MSSQL             ║
║ SaveInSentFolder            ║ 0                                                                 ║
║ ServiceAccount              ║ DOMAIN\ACCOUNT                                                    ║
║ ServiceStartMode            ║ 2                                                                 ║
║ SqlAgentAutoStart           ║ 1                                                                 ║
║ SqlAgentMailProfile         ║                                                                   ║
║ SqlAgentRestart             ║ 1                                                                 ║
║ SqlCharSet                  ║ 1                                                                 ║
║ SqlCharSetName              ║ iso_1                                                             ║
║ SqlServerRestart            ║ 1                                                                 ║
║ SqlSortOrder                ║ 52                                                                ║
║ SqlSortOrderName            ║ nocase_iso                                                        ║
║ SysAdminOnly                ║ 1                                                                 ║
║ VersionString               ║ 13.0.5081.1                                                       ║
║ WriteOemErrorLog            ║ 0                                                                 ║
╚═════════════════════════════╩═══════════════════════════════════════════════════════════════════╝

This is part of our series on SQL Server Agent.