About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

How to check the Running status of SQL Server SQL * services in one shot.

How to check the Running status of SQL Server  SQL * services in one shot.



Sometime DBA has to work with Windows Team and windows Team tell DBA to stop SQL Server Services for hardware up-gradation on the server.

So DBA has to stop  all SQL Server services and then After maintenance DBA has to start only those services which he/she stopped.

Below powershell script will help you in generating which all services  are currently running and which all are in stop state. so accordingly you can take start those services after server maintenance gets done.

# This will give status of  SQL Server  SQL * services in one screen

Get-PSGetindSQLServerServices - Function is applicable for individual server &

Get-PSGetSQLServerServices - Function will take server name and it will return the status of SQL Server services..

1. copy below script in a power shell window
Start -->  search -->  powershell ISE -->  copy paste  red color highlighted script in the window.



Function Get-PSGetindSQLServerServices ()
{
<#
  .SYNOPSIS
 Get all SQL Related services
  .DESCRIPTION
  Checks to see if the running  serverName has  all services in running state,
  .OUTPUTS
   SQL Server Services running
  .EXAMPLE
 Get-PSGetSQLServerServices

  .NOTES

  Author: Rakesh Kumar
  Website: http://Kushagrarakesh.blogspot.com
  Copyright (c) 2017 All rights reserved
.LINK
#>

clear screen

Get-Service -computername $env:COMPUTERNAME | Where-Object{$_.DisplayName -like "*SQL*"} | format-table -property name,status, DisplayName
}


in order to call this function
in other window

Get-PSGetindSQLServerServices
if you want output to store in a notepad

Get-PSGetindSQLServerServices >a

in output window type
> notepad a



Below function will take parameter as a serverName & give status of  SQL Server  SQL * services in one screen

1. copy below script in a power shell window
Start -->  search -->  powershell ISE -->  copy &  paste blue color highlighted script in the window.

Function Get-PSGetSQLServerServices ()

{


<#
  .SYNOPSIS
 Get all SQL Related services
  .DESCRIPTION
  Checks to see if the running  serverName has  all services in running state,
  .OUTPUTS
   SQL Server Services running

  .EXAMPLE:-
 Get-PSGetSQLServerServices -Servername 'XXXXXX'

  .NOTES
  Author: Rakesh Kumar
  Website: http://Kushagrarakesh.blogspot.com
  Copyright (c) 2017 All rights reserved
.LINK
#>
[cmdletbinding()]

  param(

         [Parameter( Mandatory=$true

                   , HelpMessage='The Name of the SQL Server Node to get all SQL Server services'

                   )
         ]
         [string]$ServerName

       )

Get-Service -computername $ServerName | Where-Object{$_.DisplayName -like "*SQL*"} | format-table -property Status, Name, DisplayName
}
#endregion 
Get-PSGetSQLServerServices


Compile this functions in the PowerShell Windows & then call it like this


2. To get the status of the server you specify.

Get-PSGetSQLServerServices -ServerName 'SQLServerNodeName' -ErrorAction SilentlyContinue


where SQLServerNodeName is nothing but a server name.

Thanks for reading..