Are you part of the DBA crowd that hasn’t yet checked out Powershell? I’m really enthusiastic about its potential for server administration. Script out all those mundane jobs you have to do and make life easy. Here’s a Powershell script that makes checking if services are running, and optionally starting them, on multiple servers really easy. First, be sure to check services.msc for your SQL Server service names. They may be different from the default, i.e. if you’re running Named Instances.

SQL Server Service Names 

  • Create C:\Computers.txt with a list of servers to check.
  • The $servicesArray specifies SQLSERVERAGENT and MSSQLSERVER. Change these to different services if required. Be sure to use single quotes if a $ symbol is in the service name or they will be treated as variables.
  • Set $start = $true will make the script start the listed services if they are not running.
# Setup trap to catch exceptions
trap [Exception]
{
	write-error $("TRAPPED: " + $_.Exception.Message);
}

# read computers from text file
$computers = Get-Content C:\Computers.txt;
$start = $true;

# Setup the Service array with the service names we want to check are running
$serviceArray = 'SQLAgent$SQL2005', 'MSSQL$SQL2005';

# Powershell knows it's an array so working with it is simple
foreach($computer in $computers)
{
	Write-Host "Checking $computer";
	$objWMIService = Get-WmiObject -Class win32_service -computer $computer

	foreach($service in $objWMIService)
	{
		# Check each service specicfied in the $serviceArray
		foreach($srv in $serviceArray)
		{
			if($service.name -eq $srv)
			{
				Write-Host "$srv is present on $computer.";
				if($service.state -eq "running")
				{
					Write-Host "$srv is running on $computer";
				}
				else
				{
					Write-Host "$srv is not running on $computer";
					# If $start is true the script will attempt to start the service if it is stopped
					if($start -eq $true)
					{
						# Attempt to start the current service on the current computer
						$serviceInstance = (Get-WmiObject -computer $computer Win32_Service -Filter "Name='$srv'");
						$name = $serviceInstance.Name;
						Write-Host "Attempting to start $name on $computer."
						$serviceInstance.StartService() | Out-Null;
						# Refresh the object instance so we get new data
						$serviceInstance = (Get-WmiObject -computer $computer Win32_Service -Filter "Name='$srv'");
						$state = $serviceInstance.State;
						Write-Host "$name is ""$state"" on $computer.";
					}
				}
			}
		}
	}
}

Monitoring & checking services in Powershell