DBA with PowerShell : How to get service status using PowerShell in SQL Server query

Hello friends,

This is second post of “DBA with PowerShell” series. In this series we try to fetch some useful information of SQL Instance environment

Last post “DBA with PowerShell : Get perfmon counters using PowerShell” was about fetching critical information like CPU, Memory, IO utilization and insert it  into SQL Database so we can generate meaningful reports. In this post, we will see how to fetch list of services and its status .It is very helpful in case when we need to monitor any specific service status across multiple servers.

You need to pass the machine name or just simply put “.” for local machine’s services status.

DECLARE @MachineName VARCHAR(128) = '.'  --# Put Machine Name e.g. Mac01 OR put "." for local machine
, @QueryStr VARCHAR(4000)


-- Build Query to get service info
SET @QueryStr = 'PowerShell.exe -c "$a = 1; Get-WmiObject -ComputerName '+@MachineName+'  Win32_Service | Where-Object {$_.Name -like ''*''} | SELECT SystemName, (@{Name=''Service Name''; Exp=''Name''}), (@{Name=''LogOn As''; Exp=''StartName''}), State| ForEach-Object {New-Object psObject -Property @{''SrNo''=$a;''SystemName''= [String]$a + ''^''+ $_.SystemName; ''Service Name'' = [String]$a + ''^''+$_.''Service Name''; ''LogOn As'' = [String]$a + ''^''+ $_.''LogOn As''; ''State'' = [String]$a + ''^''+ $_.State};$a ++}| Sort-Object SrNo |Format-List -Property  SystemName, ''Service Name'', ''LogOn As'', State "'

-- Insert data into a table variable
EXEC xp_cmdshell @QueryStr

-- Deleteing unwanted data

-- Converting into presentable format
SELECT [Tag],[SystemName], [Service Name] ,[LogOn As],[State]
, LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX('^',QutPut)+1,len(QutPut)))) Val
CHARINDEX('^',LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX(':',QutPut)+1,len(QutPut)))))-1) Tag
FROM @Service
MAX(Val) FOR Head IN ([SystemName], [Service Name] ,[LogOn As],[State])
) Dt


List down folders underlying file info using SQL query

Sometimes we need to list down file/folder’s information from a folder like filename, extension, created date and modified date etc.

The below query can give you the list of all content’s info from given folder path.

SQL Query:


IF OBJECT_ID('Tempdb..#FileDetails') IS NOT NULL
DROP TABLE #FileDetails

CREATE TABLE #FileDetails (FileDetails VARCHAR(MAX))

INSERT INTO #FileDetails (FileDetails)
EXEC ('EXEC XP_CMDSHELL ''powershell.exe -c "Get-ChildItem '''''+@FolderPath+''''' | SELECT Name,CreationTime,LastWriteTime,Extension|foreach{$_.Name+''''@''''+$_.CreationTime+''''|''''+$_.LastWriteTime+''''#''''+$_.Extension}"''')

SELECT SUBSTRING(FileDetails,0,CHARINDEX('@',FileDetails)) AS [File Name]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('@',FileDetails)+1,19)) AS [Creation Date Time]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('|',FileDetails)+1,19)) AS [Modified Date Time]
, ISNULL(NULLIF(SUBSTRING(FileDetails,CHARINDEX('#',FileDetails)+1,LEN(FileDetails)),''),'Directory') AS [Extension]
FROM #FileDetails

Output Content:

  Column Name Description Remarks
File Name Name of content  File name, Folder name etc.
Creation Date Time File/Folder creation date-time
Modified Date Time Last Modified date-time
Extension File extension  like.. .sql, .xlxs etc.


DBA with PowerShell : Get perfmon counters using PowerShell


DBA with PowerShell” series is to focus on gathering information about SQL Server’s infrastructure. It’s little complex to fetch such detailed information using SQL Server; whereas Windows PowerShell gives us various options and saving the PowerShell output into a database becomes more useful information for analysis perspective. I am here using PowerShell 5.0 but given script will run in lower versions as well. Windows PowerShell is a very powerful, lightweight and free scripting tool based on .Net framework. You can download it from here.

Performance monitoring is a very crucial part of every DBA. In Performance monitoring “CPU utilization”, “memory utilization” and logical/physical disk usage are very important aspects to monitor. Built-in tools like “activity monitor”, “perfmon” and sometimes third party tool are very useful but with cost; either in term of money or machine resources.

We all know that perfmon and other default tools are resources intensive; that’s why these tools are not often used in prod environment, except very specific troubleshooting requirements.

I was keep thing, if we can fetch these info form system directly and save it to database for further analysis; and then I got a chance to attend Mr. Mukesh Shende‘s PowerShell training sessions in my org.. He shared some fundamentals & advance features of PowerShell. That inspires me to write PS scripts to fetch such infrastructure’s information and save the PowerShell output into a database table. I will share all of them in my coming posts.

Perfmon data with PS Script:

The script is to fetch performance counter’s information including “CPU utilization”, “memory usage”, Physical & logical disk read/write info etc. and saves into a SQL Server database table. You need to schedule this script to collect data in required frequency.

Step 01: Creating a table to hold the information

CREATE TABLE [dbo].[SystemInfo] (
[Path] [varchar](100) NULL,
[Value] DECIMAL(18,2) NULL,
[CounterGroup] [varchar](100) NULL,

Step 02: Open PowerShell console as administration and past below to create “event log source”.

[System.Diagnostics.EventLog]::CreateEventSource("PSPerformanceCounter", "Application")

Step 03: The PS Script

You need to schedule it in SQL agent job under PowerShell type OR can execute manually for run-time information from PS-ISE.

Configure Agent Job:

1. In SSMS open “SQL Server Agent” and create a new job.

2. Assign a “Domain User” which should have “admin rights” in server and “read-write” permission on the above created table.

3. Create job step as Type as “PowerShell” and paste below code in command area.

4. Replace <SQLInstanceName> with your SQL Instance name.

5. Replace <DatabaseName> with the database, where you have created the above table.

5. Schedule it for every 5 min.

$SQLInstanceName = "<SQLInstanceName>"
$DatabaseName = "<DatabaseName>"
$Couters = 'CPU utilization,\Processor(_total)\% Processor Time','CPU utilization,\Process(*sql*)\% Processor Time','CPU utilization,\System\Processor Queue Length','Memory,\Memory\Page Reads/sec','Memory,\Memory\Page Writes/sec','Memory,\Memory\Available MBytes','Memory,\Memory\Pages/sec','Memory,\Memory\Page Faults/sec','Memory,\Paging File(_total)\% Usage','Disk IO,\LogicalDisk(*)\Disk Reads/sec','Disk IO,\LogicalDisk(*)\Disk Writes/sec','Disk IO,\LogicalDisk(*)\Avg. Disk Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Read Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Write Queue Length','Disk IO,\LogicalDisk(*)\% Free Space','Disk IO,\LogicalDisk(*)\Free Megabytes','Disk IO,\LogicalDisk(*)\Disk Transfers/sec','Physical IO,\PhysicalDisk(*)\Avg. Disk Queue Length','Physical IO,\PhysicalDisk(*)\Disk Reads/sec','Physical IO,\PhysicalDisk(*)\Disk Writes/sec','Physical IO,\PhysicalDisk(*)\Disk Transfers/sec','Network,\SQLServer:Wait Statistics(*)\Network IO waits','Network,\Network Interface(*)\Bytes Received/sec','Network,\Network Interface(*)\Bytes Sent/sec','SQL Server,\SQLServer:General Statistics\User Connections','SQL Server,\SQLServer:SQL Statistics\Batch Requests/sec','SQL Server,\SQLServer:Access Methods\Page Splits/sec','SQL Server,\SQLServer:Buffer Manager\Lazy Writes/sec','SQL Server,\SQLServer:Databases(*)\Transactions/sec','SQL Server,\SQLServer:Databases(*)\Active Transactions','SQL Server,\SQLServer:SQL Statistics\SQL Compilations/sec','SQL Server,\SQLServer:SQL Statistics\SQL Re-Compilations/sec','SQL Server,\SQLServer:Latches\Average Latch Wait Time (ms)','SQL Server,\SQLServer:Databases(*)\Data File(s) Size (KB)','SQL Server,\SQLServer:Databases(*)\Log File(s) Used Size (KB)','Locks,\SQLServer:General Statistics\Processes Blocked','Locks,\SQLServer:Locks(*)\Lock Requests/sec','Locks,\SQLServer:Locks(*)\Number of Deadlocks/sec','Locks,\SQLServer:Locks(*)\Lock Waits/sec','Locks,\SQLServer:Locks(*)\Average Wait Time (ms)','Locks,\SQLServer:Locks(*)\Lock Timeouts (timeout > 0)/sec'


$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Data Source=$SQLInstanceName;
Initial Catalog=$DatabaseName;
Integrated Security=SSPI;"


$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.connection = $conn

foreach ($ctval in $Couters)
$Couter = $ctval.Substring($ctval.IndexOf(',') + 1,$ctval.Length - $ctval.IndexOf(',') - 1)

$Prc = Get-Counter -Counter $Couter

$Val = $Prc.CounterSamples | SELECT Path,@{Name = "Timestamp"; Expression= {$_.timestamp}} , @{Name = "CookedValue"; Expression= {IF(($Couter -like '\Process(*') -OR ($Couter -like '\System*')) {$_.CookedValue/$env:number_of_processors} else {$_.CookedValue}}}

foreach ($Info in $Val)
$cmd.commandtext = "INSERT INTO SystemInfo (DateTime,Path,Value,CounterGroup)
VALUES('{0}','{1}','{2}','{3}')" -f $Info.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"),$Info.Path,$Info.CookedValue,$ctval.Substring(0,$ctval.IndexOf(','))




Write-EventLog -LogName Application -EntryType Error -source 'PSPerformanceCounter' -Message $_.Exception.Message -EventId 1234

Step 04: Enjoy 🙂


Update 17-Aug-2018: Error message has changed from “Something went wrong” to “$_.Exception.Message”. Correct message will be logged into event log under source name “PSPerformanceCounter”