Just a quick one today chaps, to show how you can restart the SQL Server Agent Service using PowerShell.
Whilst covering the Production Ops Shift in our shop over the weekend I discovered that Database Mail had not been configured for the SQL Server Agent on a number of servers. Rather than flex my right-click mouse muscles I thought I would call upon PowerShell to get the job done.
Here then is a very simple PowerShell script to restart the SQL Server Agent Service for a given instance. All you need do is call the script and pass in the servername as a parameter.
If you’re super lazy (I mean really into automation) or you have a mountain of servers to process, then you can extend this script to support the passing in of a file containing a server list, with very little effort. See my post Script SQL Server Agent Jobs using PowerShell to see exactly how you could do it.
PowerShell Script – Restart the SQL Server Agent Service (Using SMO)
# Date: 23/02/13
# Author: John Sansom
# Description: PS script to restart the SQL Server Agent Service for the provided instance
#
# Version: 1.0
#
# Example Execution: .\Restart_SQLServerAgent.ps1 ServerName
param([String]$ServerName)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
#Create a new Managed computer object for the instance
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName
$sqlagnt = $mc.Services['SQLSERVERAGENT']
Write-Host "Stopping SQL Server Agent"
$sqlagnt.Stop()
start-sleep -s 10
$sqlagnt.Start()
Write-Host "Started SQL Server Agent"
PowerShell Script – Restart the SQL Server Agent Service (Get-Service cmdlet)
Following on from an excellent suggestion by Tony in the comments, here’s a PowerShell script to perform the same operations but instead using the Get-Service cmdlet. If you prefer to not restart (stop/start) the service in one operation, you can use Get-Service to retrieve the desired service and perform a stop/start in separate operations as in the previous script.
# Date: 23/02/13 # Author: John Sansom # Description: PS script to restart the SQL Server Agent Service for the provided instance # # Version: 1.0 # # Example Execution: .\Restart_SQLServerAgent_v2.ps1 ServerName param([String]$ServerName) Get-Service -computer $ServerName SQLSERVERAGENT | Restart-Service
