Remove Older SQL Server Job Logs with Multithreaded Powershell

A short time ago I attended a WEB presentation called Speeding Up PowerShell: Multithreading and presented by Tobias Weltner. I have used Start-Job, etc. for running some tasks asynchronously but the presentation convinced me to try multiple threads instead. Watch the presentation and you will see there are many advantages to multiple threads over multiple Jobs.

I recently implemented a script to search all my SQL Servers for SQL Job logs older than 28 days and to remove them. I prefer using a centralized Powershell script to do this because it is easy to add/remove servers to a list rather than create a new task for a new server. The code presented here will operate on all my servers initiating a new Powershell thread for each server. This significantly cuts down on total processing time.

# (C) 2012 Dr. Tobias Weltner
# you may freely use this code for commercial or non-commercial purposes at your own risk
# as long as you credit its original author and keep this comment block.
# For PowerShell training or PowerShell support, feel free to contact

# Set a limit for our pool so we don't overload the machine
$throttleLimit = 4
$SessionState = []::CreateDefault()
$Pool = [runspacefactory]::CreateRunspacePool(1, $throttleLimit, $SessionState, $Host)

<#	Our script block will remove old SQL Job logs from a server.
	Keep in mind, each thread is run in it's own environment which means it does
	not automitacally enherit your sessions assemblies, variables, etc. Also, it (the thread) does 
	not include a profile. Therefore, you must make available any assemblies or 
	modules for EACH thread. Hence, the inclusion of the SQL assemplies and an
	included module with a number of SQL functions.
$ScriptBlock = {
Import-Module SqlDbAdmin

#	Number of days of logs to keep
$days = 28
$LastWrite = (Get-Date).AddDays(-$days)

#	call functions, which are in an included module, to connect to
#	SQL with SMO and to parse the server and log path into a valid UNC 
$smo = New-SMOconnection -server $server
$logpath = Get-UncPath -computername $server -path $smo.Information.ErrorLogPath

#	Remove only the selected files
get-childitem $logpath -filter '*.txt'  | 
	Where {$_.LastWriteTime -le $LastWrite} | ForEach-Object {
		Remove-Item $_.FullName
		Write-Output "Removed file $($_.FullName)"
#	End of Script Block

# 	Get an array list of all our servers
#         You will need to change this to your own path
$servers = @(Get-Content C:\Scripts\PoSH\Servers.txt)

<# 	Start a new thread for each server. The throttle we set up for our pool
	will insure the threads don't overload our machine in case we have a lot 
	of servers #>
$threads = @()
$handles = for ($x = 1; $x -le $servers.Count; $x++) {
	$server = $servers[$x]
	$powershell = [powershell]::Create().AddScript($ScriptBlock).AddArgument($server)
	$powershell.RunspacePool = $Pool
  	$threads += $powershell

#	Cycle through and close the threads as each completes
do { 
  $i = 0
  $done = $true
  foreach ($handle in $handles) {
    if ($handle -ne $null) {
  	  if ($handle.IsCompleted) {
        $handles[$i] = $null
      } else {
        $done = $false
  if (-not $done) { Start-Sleep -Milliseconds 500 }
} until ($done)

There are two Powershell Functions which are called from an included module. You can either include them in the script block or in your own module. For more information on script blocks, see the excellent article ScriptBlocks On the Fly. More information on Powershell 2.0 modules, try the article An Introduction to PowerShell Modules.

The first function you will need is used to get an SMO connection to an SQL Server.

Function New-SMOconnection {
    Param (
	Process {
	If(!(Test-Connection -ComputerName $($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to Server Machine $($server.Split('\')[0])."
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {Throw $_.Exception}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)

The second function merely creates a UNC path by joining the server machine name with the local path of ErrorLogPath we got from SMO

Function Get-UncPath {
	Join-Path "\\$($computername.split('\')[0])" $path.replace(':','$')

Leave a Reply