July 15, 2008 at 2:32 am
I'm using SQL2005 SP2, i have a problem when i kill a process then it rollsback but the rollback never completes ending up creating a block in the database. then the solution is to restart SQL server services then it cleares the rollback that ran for ages. Any solution except restarting the services??
Thx
Jnr DBA
It's better to fail while trying, rather than fail without trying!!!
July 15, 2008 at 10:27 am
Would be helpful if you specify the code over here. Usually it happens for processes that are either running from long time or are executing out of sql server space(for example Third party backup agents etc.)
Manu
July 15, 2008 at 11:47 am
I don't think that restarting SQL server is a good idea. It may cause your database in-consistent issues.
After you killed the session, it was rolling back the transactions. For a long transaction, it may take longer time to roll back.
No other method to shorten the rolling back time. (except restarting...):cool:
July 16, 2008 at 8:01 am
I had / have a similar problem with connections to DB2 (from SQL 2K5). If a specific error occurs on the DB2, the specific piece bombs, but SQL Server does not get "informed" of this. When I try and kill the job it does not work, as (I am assuming) the connection is half active... SQL Server thinks it's there, but DB2 doesn't see it as an active connection, so it doesn't listen to it.
The fix was to correct the code on our AS/400 (a trigger in the DB2), but I still need to restart the service if there is a problem on the DB2... so I am in a similar boat.
July 16, 2008 at 8:12 am
We had a similar issue with SQL 2005 linked to an Oracle database. We found that the SQL server registry settings referring to our Oracle db referenced the wrong version of the Oracle DB. After updating the registry settings and restarting services, the problem was resolved. Not sure if this is applicable to your situation but seems eerily similar.
July 16, 2008 at 10:18 am
I think it can be resolved without restarting sql server service by identifying the process sql server is trying to execute external to sql server. In my case it was Redgate backup agent and that backup process from sql went in Killed/Rollback state. I think restarting the red gate backup agent would had helped me as then sql will come to know tht process has been terminated and will gracefully end the process.
I dont know whether it will be the way I think.
Manu
July 16, 2008 at 11:23 pm
In my case the process runs from Peoplesoft application and connects through linked server to another sql box that runs a billing app. so the process from Peoplesoft insert and updates the other application and on that sql box there's a process that runs it's own updates,inserts and deletion. So the process from Peoplesoft and the Billing process access the same tables, so by terminating the Peoplesoft from billing box it terminates okay but when terminated also on Peoplesoft it does a rollback but when i check from which lsn is rolling back to it hangs there forever i use the following snytax to check SELECT DATABASE_TRANSACTION_NEXT_UNDO_LSN, DATABASE_TRANSACTION_BEGIN_LSN, DATABASE_TRANSACTION_LAST_LSN,DATABASE_TRANSACTION_MOST_RECENT_SAVEPOINT_LSN, DATABASE_TRANSACTION_COMMIT_LSN,DATABASE_TRANSACTION_LAST_ROLLBACK_LSN
FROM SYS.DM_TRAN_DATABASE_TRANSACTIONS
WHERE TRANSACTION_ID = ??, then i resort in restarting the sql service.
It's better to fail while trying, rather than fail without trying!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply