question

MpumeleloSibanda-6139 avatar image
0 Votes"
MpumeleloSibanda-6139 asked ZoeHui-MSFT commented

SQL Server Agent Step Hung Pulling From Oracle

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.

sql-server-generalazure-sql-databasesql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered ErlandSommarskog commented

So far, I have set the SQL Server Agent to log verbose information

That's the wrong place to get detailed information; the Agent only execute the SSIS package without knowing, what it do.
You have to implement the logging in the SSIS package itself, see
https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging?view=sql-server-ver15
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition: it seems likely to me that the package get stuck in Oracle, because of blocking or a bad query plan, so you talk to your Oracle DBA about how to monitor/troubleshoot the problem on that end.

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @MpumeleloSibanda-6139

Using SSIS Logging is a perfect idea.

Beside this, you can also use Profiler Trace to see what the SSIS package is sending to a SQL Server.

If there is no much message shows that the package hung in SQL Server, please consider to catch the information from Oracle side.

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

0 Votes 0 ·