7

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
3
  • What's the actual query to you are executing that is giving these results? Commented May 14, 2014 at 14:56
  • The actual query is there: "gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp"
    – Jake
    Commented May 15, 2014 at 6:18
  • I know this is not an issue as such, I just don't understand why it is happening, and that drives me crazy. If I had a reason, or I knew how to fix it, I would be content. It is just really frustrating not knowing.
    – Jake
    Commented May 22, 2014 at 23:02

4 Answers 4

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

1
  • Thanks User3465374, I tried step 10, seemed like the only relevant tip, it is still happening though. I have a picture, but can't figure out how to upload it.
    – Jake
    Commented May 15, 2014 at 6:18
1

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.

6
  • The file was being created as expected, even so, I changed it to export to a different sub folder, and it is still doing the same thing.
    – Jake
    Commented May 18, 2014 at 22:54
  • As the file is being created, could you please clarify the problem that you are trying to resolve? Commented May 21, 2014 at 8:04
  • The job has multiple steps. The first step creates this file, and puts two entries into the job history. The first entry states that the first step is currently running, the second entry says that the first step has completed successfully. I am trying to rectify the first step, as when I look through the history, even though all of the job runs state that they were successful, they all have the first entry for step 1 stating that it is still running.
    – Jake
    Commented May 22, 2014 at 22:51
  • This normally wouldn't cause issues and I would not normally have noticed it, except for the fact that I am setting up monitoring and analysis on the job history. This is throwing my analysis out as it is incorrectly stating that every execution of this job for the last 90 days is actually still running.
    – Jake
    Commented May 22, 2014 at 22:57
  • That makes sense. Commented May 23, 2014 at 7:26
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.