| Author | Claudio Silva (@claudioessilva), claudioessilva.com |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Set-DbaAgentServer on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaAgentServer.
Set-DbaAgentServer updates properties of a SQL Agent Server.
Set-DbaAgentServer updates properties in the SQL Server Server with parameters supplied.
Set-DbaAgentServer
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-InputObject] <JobServer[]>]
[[-AgentLogLevel] <Object>]
[[-AgentMailType] <Object>]
[[-AgentShutdownWaitTime] <Int32>]
[[-DatabaseMailProfile] <String>]
[[-ErrorLogFile] <String>]
[[-IdleCpuDuration] <Int32>]
[[-IdleCpuPercentage] <Int32>]
[[-CpuPolling] <String>]
[[-LocalHostAlias] <String>]
[[-LoginTimeout] <Int32>]
[[-MaximumHistoryRows] <Int32>]
[[-MaximumJobHistoryRows] <Int32>]
[[-NetSendRecipient] <String>]
[[-ReplaceAlertTokens] <String>]
[[-SaveInSentFolder] <String>]
[[-SqlAgentAutoStart] <String>]
[[-SqlAgentMailProfile] <String>]
[[-SqlAgentRestart] <String>]
[[-SqlServerRestart] <String>]
[[-WriteOemErrorLog] <String>]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Set-DbaAgentServer -SqlInstance sql1 -MaximumHistoryRows 10000 -MaximumJobHistoryRows 100
Changes the job history retention to 10000 rows with an maximum of 100 rows per job.
PS C:\> Set-DbaAgentServer -SqlInstance sql1 -CpuPolling Enabled
Enable the CPU Polling configurations.
PS C:\> Set-DbaAgentServer -SqlInstance sql1, sql2, sql3 -AgentLogLevel 'Errors, Warnings'
Set the agent log level to Errors and Warnings on multiple servers.
PS C:\> Set-DbaAgentServer -SqlInstance sql1 -CpuPolling Disabled
Disable the CPU Polling configurations.
PS C:\> Set-DbaAgentServer -SqlInstance sql1 -MaximumJobHistoryRows 1000 -MaximumHistoryRows 10000
Set the max history limitations. This is the equivalent to calling: EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000, @jobhistory_max_rows_per_job=1000
PS C:\> Set-DbaAgentServer -SqlInstance sql1 -MaximumJobHistoryRows 0 -MaximumHistoryRows -1
Disable the max history limitations. This is the equivalent to calling: EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1, @jobhistory_max_rows_per_job=0
The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or greater.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Enables piping agent server objects
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Specifies the agent log level.
Allowed values 1, "Errors", 2, "Warnings", 3, "Errors, Warnings", 4, "Informational", 5, "Errors, Informational", 6, "Warnings, Informational", 7, "All"
The text value can either be lowercase, uppercase or something in between as long as the text is correct.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | 1,Errors,2,Warnings,3,Errors, Warnings,4,Informational,5,Errors, Informational,6,Warnings, Informational,7,All |
Specifies the agent mail type.
Allowed values 0, "SqlAgentMail", 1, "DatabaseMail"
The text value can either be lowercase, uppercase or something in between as long as the text is correct.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | 0,SqlAgentMail,1,DatabaseMail |
The Agent Shutdown Wait Time value of the server agent. The accepted value range is between 5 and 600.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The Database Mail Profile to be used. Must exists on database mail profiles.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Error log file location
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Idle CPU Duration value to be used. The accepted value range is between 20 and 86400.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Idle CPU Percentage value to be used. The accepted value range is between 10 and 100.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Enable or Disable the Polling.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
The value for Local Host Alias configuration
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The value for Login Timeout configuration. The accepted value range is between 5 and 45.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Indicates the Maximum job history log size (in rows). The acceptable value range is between 2 and 999999. To turn off the job history limitations use the value -1 and specify 0 for
MaximumJobHistoryRows. See the example listed below.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Indicates the Maximum job history rows per job. The acceptable value range is between 2 and 999999. To turn off the job history limitations use the value 0 and specify -1 for MaximumHistoryRows. See
the example listed below.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
The Net send recipient value
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Enable or Disable the Token replacement property.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
Enable or Disable the copy of the sent messages is save in the Sent Items folder.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
Enable or Disable the SQL Agent Auto Start.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
The SQL Server Agent Mail Profile to be used. Must exists on database mail profiles.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Enable or Disable the SQL Agent Restart.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
Enable or Disable the SQL Server Restart.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
Enable or Disable the Write OEM Error Log.
Allowed values Enabled, Disabled
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Enabled,Disabled |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Shows what would happen if the command were to run. No actions are actually performed.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
Prompts you for confirmation before executing any changing operations within the command.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |