Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sansom - SQL Server DBA in the UK

John Sansom (Blog | Twitter) is a Microsoft Certified Master (MCM) of SQL Server and publisher of the free SQL community ebook DBA JumpStart, an inspiring collection of advice for Data Professionals, written by 20 SQL Server experts. Awarded the Microsoft Community Contributor(MCC) award, John is a prolific blogger and can be found regularly writing about SQL Server and Professional Development over at www.johnsansom.com.

Restart the SQL Server Agent Service using PowerShell

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

Comments

Leave a comment on the original post [www.johnsansom.com, opens in a new window]

Loading comments...