SQL agent job, why is the job history showing that a step is still running even though the job has completed

Question

I have a sql agent job that runs Powershell as its first step (there are 3 steps in total).

I have set this step to have 2 retries, with a 3 minute retry interval. When I look into the job history, the step_1 states that it is still running, and also that it has completed. It has done this for every time that it has run (at least the last year). Am I missing something from my powershell? Or is this something to do with sql agent itself?

Details of querying the sysjobhistory table (Pipe seperated):

Step_name|step_id|run_date|run_time|run_duration|run_status

(Job outcome)|0|2014/02/12|01:20:00|5|Succeded

Record volume space to file|1|2014/02/12|01:20:00|2|In Progress

Record volume space to file|1|2014/02/12|01:20:00|2|Succeded

Load Volume Space|2|2014/02/12|01:20:02|1|Succeded

Record database data file space|3|2014/02/12|01:20:03|2|Succeded

Step 1 runs the following Powershell:

gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp

Answer 1

On the Hey Scripting Guy! blog at Technet, Chad Miller reports that if you run a Windows Powershell command as a SQL Agent job, and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success. Check tip #10 in the post for instructions on how to implement error handling with different options so that you can, for instance, have SQL Agent continue on error or halt the Agent job. The reported "succeeded" may not in fact have succeeded.

10-tips-for-the-sql-server-powershell-scripter

Answer 2

Have you tried running the PowerShell line outside of the SQL Agent job? This would help you to identify whether the script is failing. As user3465374 has already said, the script may be failing without failing the job step.

I would also suggest amending the export to create the CSV file in a folder rather in the root of drive C. In Windows 2008 and later, the default permissions are restricted on the root of each drive. So it is possible that the export-csv command is returning an Access denied error.

Answer 3

Try simply restarting the SQL Server Agent and testing again. Your results from sysjobhistory lead me to believe that isn't because of a PowerShell error. You have a success record but you also have an in progress record. I've seen the SQL Agent act very odd sometimes and restarting it was the only thing I've figured out to fix it. For example, I've had jobs miss a schedule execution and then never run on a schedule again because of the next run time being in the past. In addition, I've definitely seen completion records not recorded even though I've never seen them doubled up like you have.

Answer 4

Bearing in mind that PowerShell will be running under the Windows Account used to launch the SQL Agent service... there's a good chance that there's either a PowerShell prompt or a GUI prompt (e.g.: UAC) causing the process to never finish from SQL Agent's point of view.

If SQL Server is running under the same Windows Account as SQL Agent then you could try executing the following from SSMS to see what happens:

exec xp_cmdshell 'powershell.exe "gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp" 2>C:\PowerShell.stderr.log 1>C:\PowerShell.stdout.log'
exec xp_cmdshell 'type C:\temp.tmp'
exec xp_cmdshell 'type C:\PowerShell.stderr.log'
exec xp_cmdshell 'type C:\PowerShell.stdout.log'

Likewise, you could put the following into a .cmd file and launch the .cmd file from the SQL Agent job instead:

powershell.exe "gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp" 2>C:\PowerShell.stderr.log 1>C:\PowerShell.stdout.log

Mind you neither approach would pick up a GUI prompt, but if there's a PowerShell prompt then it should be captured in one of the .log files.

Hope this helps, Ant.