commands

Clear

Set-DbaAgentServer


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.

Synopsis

Set-DbaAgentServer updates properties of a SQL Agent Server.

Description

Set-DbaAgentServer updates properties in the SQL Server Server with parameters supplied.

Syntax

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>]

Examples

Example: 1

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.

Example: 2

PS C:\> Set-DbaAgentServer -SqlInstance sql1 -CpuPolling Enabled

Enable the CPU Polling configurations.

Example: 3

PS C:\> Set-DbaAgentServer -SqlInstance sql1, sql2, sql3 -AgentLogLevel 'Errors, Warnings'

Set the agent log level to Errors and Warnings on multiple servers.

Example: 4

PS C:\> Set-DbaAgentServer -SqlInstance sql1 -CpuPolling Disabled

Disable the CPU Polling configurations.

Example: 5

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

Example: 6

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

Optional Parameters

-SqlInstance

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

-SqlCredential

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

-InputObject

Enables piping agent server objects


Alias
Required	False
Pipeline	true (ByValue)
Default Value

-AgentLogLevel

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

-AgentMailType

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

-AgentShutdownWaitTime

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

-DatabaseMailProfile

The Database Mail Profile to be used. Must exists on database mail profiles.


Alias
Required	False
Pipeline	false
Default Value

-ErrorLogFile

Error log file location


Alias
Required	False
Pipeline	false
Default Value

-IdleCpuDuration

Idle CPU Duration value to be used. The accepted value range is between 20 and 86400.


Alias
Required	False
Pipeline	false
Default Value	0

-IdleCpuPercentage

Idle CPU Percentage value to be used. The accepted value range is between 10 and 100.


Alias
Required	False
Pipeline	false
Default Value	0

-CpuPolling

Enable or Disable the Polling. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-LocalHostAlias

The value for Local Host Alias configuration


Alias
Required	False
Pipeline	false
Default Value

-LoginTimeout

The value for Login Timeout configuration. The accepted value range is between 5 and 45.


Alias
Required	False
Pipeline	false
Default Value	0

-MaximumHistoryRows

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

-MaximumJobHistoryRows

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

-NetSendRecipient

The Net send recipient value


Alias
Required	False
Pipeline	false
Default Value

-ReplaceAlertTokens

Enable or Disable the Token replacement property. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-SaveInSentFolder

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

-SqlAgentAutoStart

Enable or Disable the SQL Agent Auto Start. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-SqlAgentMailProfile

The SQL Server Agent Mail Profile to be used. Must exists on database mail profiles.


Alias
Required	False
Pipeline	false
Default Value

-SqlAgentRestart

Enable or Disable the SQL Agent Restart. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-SqlServerRestart

Enable or Disable the SQL Server Restart. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-WriteOemErrorLog

Enable or Disable the Write OEM Error Log. Allowed values Enabled, Disabled


Alias
Required	False
Pipeline	false
Default Value
Accepted Values	Enabled,Disabled

-EnableException

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

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.


Alias	wi
Required	False
Pipeline	false
Default Value

-Confirm

Prompts you for confirmation before executing any changing operations within the command.


Alias	cf
Required	False
Pipeline	false
Default Value