Using powershell to run SQL Jobs serially

We had some backups fail due to disk space issues.  Once we cleared the space, we needed to run the backups as I didn’t want to wait an entire day.  I didn’t want to run them at once due to contention issues and I am way too lazy to manually start each one and wait for it to finish.

Powershell to the rescue. 

The SQL provider is slick, gives you a sql server psdrive that you can explore.  Finding the jobs was easy enough.  Finding failed jobs was easy enough.  Running the jobs… wtf?  Not hard, but seems like I should be able to just start a job rather than send it as a sql command.  I hardly need the sql snapin for that.

Anyway, update SERVERNAME (and potentially instance) in the code below and it will find all your failed jobs and run them serially.

Sweet

cd SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs

$jobs  = dir | where {$_.lastrunoutcome -eq "failed" }

foreach ($job in $jobs) {

      "starting $($job.name)"

      invoke-sqlcmd -database msdb "sp_start_job '$($job.name)'"

      while ((invoke-sqlcmd -database msdb "sp_help_jobactivity @job_id = NULL, @job_name =  '$($job.name)'").run_status -ne 1) {

            "still running..."

            sleep 120

      }

      "Completed at $(date)"

}

Timeouts Removing VMWare snapshots w/ PowerCLI

On commit of several of our vmsnapshots we are getting the annoy “Operation Timed Out” after the vcenter timeout limit is reached.  Luckily, the remove snapshot operation is still performed in the background and eventually completes.  Below is a quick and dirty way to delete a vm snapshot and wait for the process to complete.  I check the existence of the snapshot every 60 seconds and return when it is gone.  If it is still going after 2 hours, I throw an error.  It is pretty noisy as I wanted to see what was happening but didn't feel like getting into logging, you will probably want to add to it if you need to actually use it. 

This is to be run in a PowerCLI window.  Add the function and then you can use
    delete-vmsnapshot "VMName"

Note: if you add -confirm:$false to the remove-snapshot line, you won't be prompted to confirm the delete. I took it out of the below to add some protection against copying and pasting code.  B)

Note 2: if you don't care about how monitoring how long it takes you can just do something like the following.  I wanted to loop through several serially and didn't want them starting over each other.
     get-vm -name "VMNAME" | get-snapshot | remove-snapshot

function delete-vmsnapshot {

      param ($vmname)

       $d1 = date

       "Starting at $d1"

       $vm = get-vm -name $vmname

       $sn = $vm | get-snapshot

       "Deleting:"

       $sn | fl *

       $sn | remove-snapshot 

       $timesleeping = ((date) - d1).TotalSeconds

       while (-not (($vm | Get-Snapshot) -eq $null)) {

            $timesleeping = ((date) - d1).TotalSeconds

            if ($timesleeping -gt 7200) {

                  throw ("waiting over 2 hours for snapshot to delete")

            } else {

                  "Merging for $timesleeping seconds."

            }

            sleep 60

      }

      $d2 = ((date) - d1).TotalSeconds

      "Complete taking a total of $d2 seconds" 

}