SQL Agent Job Running 8 Days - Not Doing Anything

  • I have a job in my environment that truncates a SQL Server table, queries an Oracle database and uses that in an INSERT to populate data in SQL Server 2012.  I discovered that it has been running for 8 days and causing blocking every night since 2/1.  It isn't being blocked by any other query.  When I do a SELECT from the table using NOLOCK I get back zero rows.  I'm guessing that means that the table has been truncated and no rows have yet been inserted.  When I test the SELECT to the Oracle database it comes back in 4 seconds.  This should mean I'm not waiting on Oracle. The result set from Oracle is only 291 rows so it shouldn't take long for this job to do its work.  In fact, the job normally finishes in 4 seconds.   I executed a KILL command but now sp_who shows the session is in the Kill/rollback phase.Been that way for 30 minutes.  

    If I restart SQL Server, is the recovery phase for bringing the database online any different from the work that ROLLBACK does for a particular sessions queries?  In other words, will I still be waiting a long time for the database to recover?  The log for this database is only 185 MB by the way.

  • I think your case is about distributed transaction (linked server). If so, about your case, I think a restart is needed to complete the rollback.

    I guess, the difference is, rollback in a restart is a regular local one. Without restart, rollback is a process by Microsoft Distributed Transaction Coordinator (MS DTC). But I'm not so sure.

    BTW, it might not help, but I suggest you SET XACT_ABORT ON.

    GASQL.com - Focus on Database and Cloud

  • lmarkum - Friday, February 9, 2018 8:34 AM

    I have a job in my environment that truncates a SQL Server table, queries an Oracle database and uses that in an INSERT to populate data in SQL Server 2012.  I discovered that it has been running for 8 days and causing blocking every night since 2/1.  It isn't being blocked by any other query.  When I do a SELECT from the table using NOLOCK I get back zero rows.  I'm guessing that means that the table has been truncated and no rows have yet been inserted.  When I test the SELECT to the Oracle database it comes back in 4 seconds.  This should mean I'm not waiting on Oracle. The result set from Oracle is only 291 rows so it shouldn't take long for this job to do its work.  In fact, the job normally finishes in 4 seconds.   I executed a KILL command but now sp_who shows the session is in the Kill/rollback phase.Been that way for 30 minutes.  

    If I restart SQL Server, is the recovery phase for bringing the database online any different from the work that ROLLBACK does for a particular sessions queries?  In other words, will I still be waiting a long time for the database to recover?  The log for this database is only 185 MB by the way.

    It may or may not go through the same recovery process - sometimes it comes right back online and others it starts the recovery and rollback process again. But it doesn't seem like there would be much to roll back. Before restarting, take a look at what's in the Distributed Transaction Coordinator. I've found things stuck in there before and a restart of the DTC coordinator was all that was needed. Start Run dcomcnfg - > Computers - My Computer - Distributed Transaction Coordinator.

    Sue

  • Sue_H - Friday, February 9, 2018 11:25 AM

    lmarkum - Friday, February 9, 2018 8:34 AM

    I have a job in my environment that truncates a SQL Server table, queries an Oracle database and uses that in an INSERT to populate data in SQL Server 2012.  I discovered that it has been running for 8 days and causing blocking every night since 2/1.  It isn't being blocked by any other query.  When I do a SELECT from the table using NOLOCK I get back zero rows.  I'm guessing that means that the table has been truncated and no rows have yet been inserted.  When I test the SELECT to the Oracle database it comes back in 4 seconds.  This should mean I'm not waiting on Oracle. The result set from Oracle is only 291 rows so it shouldn't take long for this job to do its work.  In fact, the job normally finishes in 4 seconds.   I executed a KILL command but now sp_who shows the session is in the Kill/rollback phase.Been that way for 30 minutes.  

    If I restart SQL Server, is the recovery phase for bringing the database online any different from the work that ROLLBACK does for a particular sessions queries?  In other words, will I still be waiting a long time for the database to recover?  The log for this database is only 185 MB by the way.

    It may or may not go through the same recovery process - sometimes it comes right back online and others it starts the recovery and rollback process again. But it doesn't seem like there would be much to roll back. Before restarting, take a look at what's in the Distributed Transaction Coordinator. I've found things stuck in there before and a restart of the DTC coordinator was all that was needed. Start Run dcomcnfg - > Computers - My Computer - Distributed Transaction Coordinator.

    Sue

    Sue, The transaction list is empty, but the Transactions Statistics show Current: Max Active as 1.

  • lmarkum - Friday, February 9, 2018 12:08 PM

    Sue_H - Friday, February 9, 2018 11:25 AM

    lmarkum - Friday, February 9, 2018 8:34 AM

    I have a job in my environment that truncates a SQL Server table, queries an Oracle database and uses that in an INSERT to populate data in SQL Server 2012.  I discovered that it has been running for 8 days and causing blocking every night since 2/1.  It isn't being blocked by any other query.  When I do a SELECT from the table using NOLOCK I get back zero rows.  I'm guessing that means that the table has been truncated and no rows have yet been inserted.  When I test the SELECT to the Oracle database it comes back in 4 seconds.  This should mean I'm not waiting on Oracle. The result set from Oracle is only 291 rows so it shouldn't take long for this job to do its work.  In fact, the job normally finishes in 4 seconds.   I executed a KILL command but now sp_who shows the session is in the Kill/rollback phase.Been that way for 30 minutes.  

    If I restart SQL Server, is the recovery phase for bringing the database online any different from the work that ROLLBACK does for a particular sessions queries?  In other words, will I still be waiting a long time for the database to recover?  The log for this database is only 185 MB by the way.

    It may or may not go through the same recovery process - sometimes it comes right back online and others it starts the recovery and rollback process again. But it doesn't seem like there would be much to roll back. Before restarting, take a look at what's in the Distributed Transaction Coordinator. I've found things stuck in there before and a restart of the DTC coordinator was all that was needed. Start Run dcomcnfg - > Computers - My Computer - Distributed Transaction Coordinator.

    Sue

    Sue, The transaction list is empty, but the Transactions Statistics show Current: Max Active as 1.

    I restarted the DTC service on the Windows machine, but the session is still open and shows it is still running the same INSERT statement in SQL Server.

  • lmarkum - Friday, February 9, 2018 12:17 PM

    lmarkum - Friday, February 9, 2018 12:08 PM

    Sue_H - Friday, February 9, 2018 11:25 AM

    It may or may not go through the same recovery process - sometimes it comes right back online and others it starts the recovery and rollback process again. But it doesn't seem like there would be much to roll back. Before restarting, take a look at what's in the Distributed Transaction Coordinator. I've found things stuck in there before and a restart of the DTC coordinator was all that was needed. Start Run dcomcnfg - > Computers - My Computer - Distributed Transaction Coordinator.

    Sue

    Sue, The transaction list is empty, but the Transactions Statistics show Current: Max Active as 1.

    I restarted the DTC service on the Windows machine, but the session is still open and shows it is still running the same INSERT statement in SQL Server.

    Dang...was hoping that would work. Sometimes does, sometimes doesn't. And yeah the Transaction Statistics is what you would want to look at - sorry I left that out. It may just be time to bite the bullet now and restart the SQL service. Here is hoping it's one that goes fast...post back if you restart. I'd like to know if it was quick or not. I still keep thinking it would be quick since you found the number of rows affected and it wouldn't be much.

    Sue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply