Troubleshooting of a job running problem

Today, a colleague from the product line came over and said that two jobs on a database server appeared to have been dead there. After several hours, the job status has been executing. This kind of DEBUG is definitely not a newbie like me, but I stood behind the manager and probably learned his troubleshooting process.

1. Run sp_who2 to check whether other processes have blocked the job.
Result: No connection was found between the job and the database server. (Job itself is an SSIS application used to load data from files to the database)

2. Run DBCC opentran
It may be something like a deadlock in a transaction, but no result is displayed.

3. DBCC imputbuffer (spid)
After checking the document, we can see that this is the last statement used to return a client process to an SQL server instance.

4. log on to the database server remotely to view the resource usage of the dtexec.exe process.
Our database server is 64-bit, and this command is used to deploy or run the SSIS package on a 64-bit machine. Here is a simple call example:
Exec xp_cmdshell 'dtexec/F "C: \ upsertdata. dtsx "'
Result: No too many results were found.

5. The next step is to carefully analyze the table in MSDB that records job execution
Select * From sysjobhistory
Select * From sysjobs
The final conclusion is that when the two jobs are started, another job is also started. As mentioned above, all these jobs need to run SSIS packages and require a proper amount of memory. On our database server, we set a maximum value for the memory that SQL server can use. This may cause a situation where SQL Server eats all the memory, resulting in insufficient memory of the SSIS process, so it is inexplicably killed.

Of course, the final conclusion is far-fetched. It is purely speculative that we need to change the maximum memory usage of the database server, and then see if similar errors will occur in the future...