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.

Ads by Google, Paying the Rent: