Tag Archives: job

SQL Server 2012 Uses PowerShell V2 Even If It Is On Windows Server 2012

Wow! PS version 3 is great! A host of beneficial changes. If you’re like me, you probably wanted to start using it right away to take advantage of some of the new and wonderful changes.

But, HOLD ON! If you are wanting to implement any of those new and upgraded cmdlets in SQL Server 2012, you can’t. Even if you are running on Windows Server 2012, you still can’t use PS 3 cmdlets. Yes, that’s right. The reason is SQL 2012 uses PS Version 2 internally. Check it out. Here is a screen shot of the $PSVersionTable command in SQL Server 2012 on Windows Server 2012 (after right-clicking a database and selecting Start PowerShell):

Here’s a screen shot of $PSVersionTable in Powershell on the same server

So, If you want to run PS 3 for any job steps on SQL 2012, you will need to choose the command type as Operating System (CmdExec) and not PowerShell.

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 tobias.weltner@email.de

# Set a limit for our pool so we don't overload the machine
$throttleLimit = 4
$SessionState = [system.management.automation.runspaces.initialsessionstate]::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(':','$')

Asynchronous Processing using Powershell Jobs

Powershell is a fantastic tool to use for management of multiple computers. I have been slowly converting many of our administrative functions from a hodge-podge mixture of CMD, BAT, VPS, Python, and Perl scripts. One daily administrative talks is copying a variety of backups from a variety of Windows servers to our data ’warehouse’ where they are then copied to tape.

I recently found the “Jobs” cmdlets in Powershell V2.0. With “Jobs” you can asynchronously process multiple tasks (e.g. copying backups from many remote machines to data storage on the current local machine). In my case, running the copies synchronously results in the process spanning over to the next morning. Backups are stored daily. If a backup is taken on Wednesday, I can’t have it being stored in our warehouse under Thursday.

Continue reading