powershell via sql server agent

In short, I have a PS script that works via the ISE but not via a SQL Server Agent Job, but it used to work for both!

I will provide more details. The script is powershell 2.0 that is scheduled to be invoked via SQL Server Agent. It invokes other PS scripts. It has worked for a long time. This script is a SQL Server Agent Step of several. There's only one recent change on the server. On the host server, the ISE was not installed. So I had to install the PS ISE and issue the following commands:

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

Open in new window


This installed the PS ISE successfully. Other than that one change, I did have a problem, which is why I had to install the ISE.
I have a PS script that goes out to SQL Server Logs and gets yesterday's entries and then emails the results to admins. That script was having problems due to the size of a log being gigantic on a particular server. After recycling the log the script stopped "hanging" and would process via the ISE, but it won't work via the SQL Server Agent Job.

So it works in the ISE but not via SQL Server Agent. How can I generate an error so I know what to fix?

Does anyone have an idea as to why this would occur?

Thanks,

pae2
pae2Asked:
Who is Participating?
 
pae2Author Commented:
It turns out (handled quite some time ago) that recycling the logs on a few servers did the trick. The logs were too long and the script was unable to process those logs in a reasonable amount of time.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
My approach would be to write intermittent messages to a file, dumping some of the vars (e.g. with get-variable *), if there is no trace.
0
 
Rainer JeschorCommented:
Hi,
nothing in the SQL Server error log?
Does the agent job fail? If it fails, anything in the jobs history?

Does the job run with the SQL Agent credentials? Or did you configure a proxy account?
Which version of SQL server?

HTH
Rainer
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
pae2Author Commented:
Rainer, good questions. There is nothing in the log. The agent job just hangs. So there's nothing in the history. The Agent job does use a proxy account. The script is hosted on R2 but connects to 2005 - 2014 instances; any ideas?

Thanks!

pae2
0
 
pae2Author Commented:
Qlemo would you be able to provide a small sample of what you're talking about?

Thanks!

pae2
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
It seems that something is missing in the logs now that you recycled them.  It would depend on what is in your scripts.  Recycling the log and installing the ISE should not cause a job to hang when it worked before.

So I would look at the script and it if does any sort of output it could cause the job to appear to hang.  You could add logging to the powershell script to see which statement it is getting to.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Example code
# Do something
"Did something #1" | Out-File -Append "C:\Temp\debugging.log"
# Do something else, getting n objects
"Did something #2, and got {0} rows" -f $obj.Count | Out-File -Append "C:\Temp\debugging.log"

Open in new window


You are saying the job hangs - do you mean it does not finalize, or you do not see it doing anything but it finishs?
0
 
pae2Author Commented:
DBAduck - Ben Miller, I will try to add logging to the script tomorrow at work. That's a good idea. Thanks, pae2
0
 
pae2Author Commented:
Qlemo it does not finalize. And I will see if I can write messages to a file tomorrow at work, as illustrated. Thanks, pae2
0
 
pae2Author Commented:
Recycling the logs on a few servers where the logs were extremely long allowed the PS scripts to run within a normal amount of time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.