Hello techies,
I have what I believe is a DBA question. DBA is not my core skill, and I am stuck.
Context
Our system is set up to pull data from Oracle. This is done via SQL Server Agent jobs running SSIS packages. Occasionally, we get some silent hung operations. Under normal circumstances, the process normally takes about 3 hours to pull the data. When the process hangs, it can take more than five hours with nothing happening or even a day. At one time, it took two days stuck on that step.
Question
Is there a way that I can use in SQL Server to peer into what exactly will be happening at hung times? I want to monitor and capture the reason for hanging. So far, I have set the SQL Server Agent to log verbose information following the advice given by Greg on https://www.mssqltips.com/sqlservertip/1394/how-to-store-longer-sql-agent-job-step-output-messages/. There has not been a hung process yet to establish if the setup works since I put it in place.
Your help will be highly appreciated.