On a SQL Server instance (Microsoft SQL Server 2017 (RTM-CU31)), I have a job that calls a Python script that makes an API call to return data. This job now and again stops processing (no increase in reads, writes, logical reads are my tell-tale) and I cannot kill the SPID, this leaves the SQL Server Agent thinking the job step is still running so it will not 'finish' the job.
As this instance is a fairly quiet one I had permission to bounce SQL and that got rid of the stuck process from SQL Server's perspective, but it is not a method I employed eagerly. I am assuming this OS process will remain a zombie process until the box is restarted at the next patching job.
Is there a way of identifying the xp_cmdshell process at an OS level perhaps, and trying to kill it via CLI? Or could anyone offer any other advice on what my recourse could be in this situation?
For info we have no DBA and I'm a DEV that is learning as much admin things as possible but there are only so many hours in a day!