by widba at 2012-11-08 13:52:05
I have a script that performs database moves along with attach/reatach processes. At the end of the process a removal of the old database files needs to take place. If I perform a straight remove-item the SQL agent job returns fine after the completion of the deletion. The issue I had was the delete was taking too much time and this job was part of a queue so it was holding up more processing. I setup the Remove-Item into a job so I could simply fire it, and the processing could continue. Running this from the command line or via ISE works great – however once I set this up in a job on a SQL Server – Operating System Step – the SQL job fails on the call to the "start-job". If I simply remove the Start-Job and run the remove – I am good in SQL Agent.
I have tried a variety of workarounds, not sure what the agent is seeing that prompts it to fail.
Any experience or workarounds?
by DonJ at 2012-11-08 15:01:03
Which version of SQL?
by widba at 2012-11-08 15:09:18
2012 Enterprise Edition
by DonJ at 2012-11-08 15:21:04
Well, I suspect that when SQL Agent runs a PowerShell task, the actual PowerShell process only lasts until the command completes. Start-Job completes instantly. If SQL Agent then lets the process go – and there's no reason it wouldn't, as the process would look "done" and idle – then the job gets summarily killed. It's kind of like using Invoke-Command to start a job. Job starts, shell closes, job vanishes before it actually finishes. Because PowerShell would be running in-process with Agent, there'd be no way to "detach" it and let it run independently.
I'm not entirely sure why Agent is interpreting this as a fail... but my suspicion is you won't get it to work that way. What might be better is to have Agent schedule a task in Task Scheduler, to run immediately, and have the task handle the deletion. That way the task itself is outside of Agent, and can proceed without it. If you happen to be on Win2012, you can do that with the ScheduledTasks module; if not, the good old Schtasks.exe command should do it.
by widba at 2012-11-08 15:48:02
I suspected it was something with the start-job itself, but I bet if I put a start-sleep in that would cover the run time it would work. Your explanation makes more sense. Wonder, if I create a job the old way like we had to on an NT domain (have start-job spawn a new powershell process)- that might work so its a disconnected process (or maybe its still a child process) Something to try tomorrow.
Thanks for the info, I appreciate it.
by cmille19 at 2012-11-09 13:02:41
Rather than doing this in Powershell or Scheduled Tasks, it's a lot easier just to use SQL Server Agent jobs. Simply setup a SQL Server Agent job with the Powershell command you want to run without using start-job, then call the second SQL Server Agent job from the first SQL Server Agent job using sp_start_job in an execute SQL job step task. Once you start the SQL Server Agent job using sp_start_job the step will immediately return success (as long as it starts) and won't wait for the second SQL Server Agent job to complete.
by widba at 2012-11-09 13:13:55
That is a great idea. The only issue I will have to workaround is I am moving SSAS databases, and their underlying folders have revision names embedded, so there is some "intelligence" in the script when managing the scripts. Certainly something I can work around though.
by DonJ at 2012-11-09 15:22:46
SQL Agent can still do VBS, can't it? Plenty of intelligence there.
by cmille19 at 2012-11-09 15:55:07
[quote="widba"]That is a great idea. The only issue I will have to workaround is I am moving SSAS databases, and their underlying folders have revision names embedded, so there is some "intelligence" in the script when managing the scripts. Certainly something I can work around though.[/quote]
I think you're misunderstanding my suggestion:
1. Use Powershell just don't use start-job in your Powershell script
2. Setup a new SQL Agent job with just the Powershell script you want to run async as a separate SQL Agent Job
3. Add a job step to your original SQL Agent job that calls sp_start_job on your new SQL Agent Job
This is an old DBA technique for running tasks in parallel or async. The call to sp_start_job immediately returns success or failure on starting the job, but does not wait for job to complete. If you really want to you could check the other job from the first job, wait and even fail the first job by simply calling raiserror based on the results of the second job.