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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", 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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
Qlemo"Batchelor", 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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.