SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent Job Running 8 Days - Not Doing Anything


SQL Agent Job Running 8 Days - Not Doing Anything

Author
Message
lmarkum
lmarkum
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6803 Visits: 1065
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.
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 Visits: 264
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
Sue_H
Sue_H
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74661 Visits: 15266
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



lmarkum
lmarkum
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6803 Visits: 1065
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
lmarkum
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6803 Visits: 1065
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.

Sue_H
Sue_H
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74661 Visits: 15266
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search