sql serversql-server-agent
We have a SQL Agent job running some PowerShell, which creates a pretty long output (it has robocopy output, among other things).
In the SQL Agent Job History, we only see the first 1,000 characters or so of this output.
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.
Is there any way to extract the full text of these messages?
You can achieve it in 2 ways - Go in job step and select the Advanced tab:
a. Output to a file (<== My preferred method)
b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of nvarchar(1024))
In order to see the additional logged information you need to use this stored procedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogs table directly.
Best Answer
In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename. That should include all of your output for later viewing.
Or, if you want all of the output directly in the history of the job, you can select the check box "Include step output in history". It'll use history entry rollover to include all of the data. That might be a little cumbersome in looking at after-the-fact, though, but still effective.