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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 |
DECLARE @MasterPath nvarchar(512); DECLARE @LogPath nvarchar(512); DECLARE @ErrorLog nvarchar(512); DECLARE @ErrorLogPath nvarchar(512); SELECT @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(N'\', reverse(physical_name))) FROM master.sys.database_files WHERE name=N'master'; SELECT @LogPath=substring(physical_name, 1, len(physical_name) - charindex(N'\', reverse(physical_name))) FROM master.sys.database_files WHERE name=N'mastlog'; SELECT @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512)); SELECT @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex(N'\', reverse(@ErrorLog))); DECLARE @SmoRoot nvarchar(512); EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\Setup' , N'SQLPath' , @SmoRoot OUTPUT; IF OBJECT_ID('tempdb..#tmp_sp_get_sqlagent_properties') IS NOT NULL DROP TABLE #tmp_sp_get_sqlagent_properties; CREATE TABLE #tmp_sp_get_sqlagent_properties ( auto_start int null , msx_server_name sysname null , sqlagent_type int null , startup_account nvarchar(255) null , sqlserver_restart int null , jobhistory_max_rows int null , jobhistory_max_rows_per_job int null , errorlog_file nvarchar(255) null , errorlogging_level int null , error_recipient nvarchar(30) null , monitor_autostart int null , local_host_server sysname null , job_shutdown_timeout int null , cmdexec_account varbinary(64) null , regular_connections int null , host_login_name sysname null , host_login_password varbinary(512) null , login_timeout int null , idle_cpu_percent int null , idle_cpu_duration int null , oem_errorlog int null , sysadmin_only int null , email_profile nvarchar(64) null , email_save_in_sent_folder int null , cpu_poller_enabled int null , replace_alert_tokens_enabled int null ); INSERT INTO #tmp_sp_get_sqlagent_properties ( auto_start , msx_server_name , sqlagent_type , startup_account , sqlserver_restart , jobhistory_max_rows , jobhistory_max_rows_per_job , errorlog_file , errorlogging_level , error_recipient , monitor_autostart , local_host_server , job_shutdown_timeout , cmdexec_account , regular_connections , host_login_name , host_login_password , login_timeout , idle_cpu_percent , idle_cpu_duration , oem_errorlog , sysadmin_only , email_profile , email_save_in_sent_folder , cpu_poller_enabled , replace_alert_tokens_enabled ) EXEC msdb.dbo.sp_get_sqlagent_properties; DECLARE @DatabaseMailProfile nvarchar(255); DECLARE @AgentMailType int; DECLARE @ServiceStartMode int; DECLARE @ServiceAccount nvarchar(512); DECLARE @AgtGroup nvarchar(512); EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' , N'DatabaseMailProfile' , @param = @DatabaseMailProfile OUT , @no_output = N'no_output'; EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' , N'UseDatabaseMail' , @param = @AgentMailType OUT , @no_output = N'no_output'; EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE' , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT' , N'Start' , @ServiceStartMode OUTPUT; EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE' , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT' , N'ObjectName' , @ServiceAccount OUTPUT; EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\Setup' , N'AGTGroup' , @AgtGroup OUTPUT; SELECT Items.* FROM #tmp_sp_get_sqlagent_properties AS tsgsp CROSS APPLY (VALUES ('Name', CAST(serverproperty(N'ServerName') AS sql_variant)) , ('MsxServerName', ISNULL(tsgsp.msx_server_name,N'')) , ('JobServerType', CONVERT(sql_variant, tsgsp.sqlagent_type)) , ('SqlServerRestart', CONVERT(sql_variant, tsgsp.sqlserver_restart)) , ('SqlAgentRestart', CONVERT(sql_variant, tsgsp.monitor_autostart)) , ('MaximumHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows)) , ('MaximumJobHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows_per_job)) , ('ErrorLogFile', CONVERT(sql_variant, tsgsp.errorlog_file)) , ('AgentLogLevel', CONVERT(sql_variant, tsgsp.errorlogging_level)) , ('NetSendRecipient', CONVERT(sql_variant, ISNULL(tsgsp.error_recipient,N''))) , ('AgentShutdownWaitTime', CONVERT(sql_variant, tsgsp.job_shutdown_timeout)) , ('SqlAgentMailProfile', CONVERT(sql_variant, ISNULL(tsgsp.email_profile,N''))) , ('SaveInSentFolder', CONVERT(sql_variant, CAST(tsgsp.email_save_in_sent_folder AS bit))) , ('WriteOemErrorLog', CONVERT(sql_variant, CAST(tsgsp.oem_errorlog AS bit))) , ('IsCpuPollingEnabled', CONVERT(sql_variant, CAST(tsgsp.cpu_poller_enabled AS bit))) , ('IdleCpuPercentage', CONVERT(sql_variant, tsgsp.idle_cpu_percent)) , ('IdleCpuDuration', CONVERT(sql_variant, tsgsp.idle_cpu_duration)) , ('LoginTimeout', CONVERT(sql_variant, tsgsp.login_timeout)) , ('HostLoginName', CONVERT(sql_variant, ISNULL(tsgsp.host_login_name,N''))) , ('LocalHostAlias', CONVERT(sql_variant, ISNULL(tsgsp.local_host_server,N''))) , ('SqlAgentAutoStart', CONVERT(sql_variant, tsgsp.auto_start)) , ('ReplaceAlertTokensEnabled', CONVERT(sql_variant, tsgsp.replace_alert_tokens_enabled)) , ('DatabaseMailProfile', CONVERT(sql_variant, ISNULL(@DatabaseMailProfile,N''))) , ('AgentMailType', CONVERT(sql_variant, ISNULL(@AgentMailType, 0))) , ('SysAdminOnly', CONVERT(sql_variant, 1)) , ('ServiceStartMode', CONVERT(sql_variant, @ServiceStartMode)) , ('ServiceAccount', CONVERT(sql_variant, ISNULL(@ServiceAccount,N''))) , ('AgentDomainGroup', CONVERT(sql_variant, ISNULL(suser_sname(sid_binary(ISNULL(@AgtGroup,N''))),N''))) )Items(ItemName, ItemValue) UNION ALL SELECT Items.* FROM (VALUES ('IsCaseSensitive', CAST(case when 'a' <> 'A' then 1 else 0 end AS bit)) , ('MaxPrecision', @@MAX_PRECISION) , ('ErrorLogPath', @ErrorLogPath) , ('RootDirectory', @SmoRoot) , ('IsFullTextInstalled', CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit)) , ('MasterDBLogPath', @LogPath) , ('MasterDBPath', @MasterPath) , ('VersionString', SERVERPROPERTY(N'ProductVersion')) , ('Edition', CAST(SERVERPROPERTY(N'Edition') AS sysname)) , ('ProductLevel', CAST(SERVERPROPERTY(N'ProductLevel') AS sysname)) , ('IsSingleUser', CAST(SERVERPROPERTY('IsSingleUser') AS bit)) , ('EngineEdition', CAST(SERVERPROPERTY('EngineEdition') AS int)) , ('Collation', convert(sysname, serverproperty(N'collation'))) , ('NetName', CAST(SERVERPROPERTY(N'MachineName') AS sysname)) , ('IsClustered', CAST(SERVERPROPERTY('IsClustered') AS bit)) , ('ResourceVersionString', SERVERPROPERTY(N'ResourceVersion')) , ('ResourceLastUpdateDateTime', SERVERPROPERTY(N'ResourceLastUpdateDateTime')) , ('CollationID', SERVERPROPERTY(N'CollationID')) , ('ComparisonStyle', SERVERPROPERTY(N'ComparisonStyle')) , ('SqlCharSet', SERVERPROPERTY(N'SqlCharSet')) , ('SqlCharSetName', SERVERPROPERTY(N'SqlCharSetName')) , ('SqlSortOrder', SERVERPROPERTY(N'SqlSortOrder')) , ('SqlSortOrderName', SERVERPROPERTY(N'SqlSortOrderName')) , ('BuildClrVersionString', SERVERPROPERTY(N'BuildClrVersion')) , ('ComputerNamePhysicalNetBIOS', SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS')) )Items(ItemName, ItemValue) ORDER BY Items.ItemName; |
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.