Powershell: Notify when query stops

Right here we go, first post on new blog and I though I’d start with something fun!

Recently I was working on a task purging data from a database, the query was deleting rows in batches and would typically take 45 minutes or more to complete. I was finding I was skipping back and forth looking to see when the query had stopped.

This was not the best use of my time so I decided to have a bit of fun in my spare time and write a little Powershell script that would monitor my sessions and notify me when it stopped so I could focus on other things and only jump back when I knew it was all done.

So here it is!

$SessionID = 101

$sqlCmdSplat = @{
    serverinstance="localhost"
    database="WideWorldImporters"
    username="sql_pain"
    password= $(Read-Host -Prompt "EnterPassword")
    query="SELECT 1 FROM sys.dm_exec_requests WHERE session_id = $sessionID"
}

while(1 -eq 1)
{
    clear
    try{
        $result=invoke-sqlcmd @sqlCmdSplat
        if (!($result))
        {
            (New-Object System.Media.SoundPlayer $(Get-ChildItem -Path "$env:windir\Media\tada.wav").FullName).Play()
            Write-Host "Session finished"
            break;
        }
    }
    catch {
        write-host "Great Scott something's gone wrong!"
        break;
    }
    Start-Sleep -Seconds 10  
}

Obviously this is very basic with not much validation, so if you cannot connect to the target it will return as if the query has finished but it was only meant to be a quick and dirty script to have some fun with.