views:

196

answers:

6

I need to determine if a specific application is running from a SQL Server 2005 job. The issue is that one of our applications we use to send data will hang, causing problems with any subsequent jobs that invokes it. If I can also obtain the CPU time, I can determine if it's likely a hung process.

A list of running applications would be good, but being able to lookup a specific executable name with the CPU time would be fantastic!

A: 

I think the best approach is to run SQL Server Profiler as well as performance monitor and wait for the specified job to run. Then import the perfmon stats into profiler. You can do this from SQL Server profiler by going to File–> Import Performance Data… and point it to your Performance Monitor logs.

You should be able to choose the Process(all) counter to give you a list of all running processes, as well as getting CPU time for the processes. You can then correlate this to the application name and/or hostname from the Profiler logs to see whats going on.

Nick Kavadias
Running Profiler constantly in the background to detect this isn't a valid solution within a production environment.
Derek B. Bell
if you can use the filters on profiler well enough, then the load is not that much more for the server to handle. You can also run profiler on another server that is pointed to your production SQL Server to lighten the load even further.
Nick Kavadias
A: 

Try opening the SQL Server Activity Monitor. You can also get some of the information from the stored proc sp_who2.

ck
A: 

Have the job run an external script (batch file, KSH script) instead of a TSQL script.

BradC
A: 

You should create a CLR stored procedure here which is called from within the job. Inside the procedure, you can call use the Process class or the PerformanceCounter class (the latter is the better choice if you want CPU utilization although the Process class has how much time the process has used the CPU through the UserProcessorTime and TotalProcessorTime properties) and get your values.

You can then use the Process class by calling the static GetProcessesByName method to get an array of the instances of your process by name and then call the Kill method on the appropriate instance.

IMO, it's better than a script since it's integrated into SQL Server, and while the CLR code is running, it is controlled by SQL server, whereas with scripts, SQL Server can't take into account memory allocation and processor time that the script is consuming.

casperOne
+1  A: 

Any application launched by a job step will show as being run by the same logon account as the SQL Server Agent. Use a specific service account for the SQL Server Agent that won't be used for any other services. This willallow you to monitor the applications launched from by a job using Task Manager, Performance Monitor, etc.

cmsjr
A: 

I use the (free) replacement to task manager "Process Explorer" to get a better look at exe's and their dependencies.

Might be worth monitoring your issue with this.

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Chris Simpson
That doesn't help me within a SQL Server job.
Derek B. Bell