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