Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I had a large update statement run over the weekend that took approximately 6.5 hours to complete. That was 2 days ago (friday evening, today is monday morning). The table has 77million records in it, and about 10 fields were updated. Monday morning I logged in to check my update results and the table is locked. I have a COMMIT TRANSACTION at the end of the stored procedure, so I don't know what is goig on with the table?

I don't want to kill the SPID either, because it might roll back whatever it is doing.

Help! What can I do to get

Thanks in advance.

share|improve this question
add comment

1 Answer

If it's still running, you have two options: let it finish and commit the work, or kill the process and thus roll back the transaction.

If it's finished, and it just didn't commit the open transaction for whatever reason, find the system that connection is from so you can manually commit the transaction rather than killing it and rolling back.

share|improve this answer
    
Process has completed. How can I manually commit? –  EWizard May 9 '11 at 13:11
    
The stored procedure was run in SSMS manually. –  EWizard May 9 '11 at 13:32
    
DBCC OPENTRAN reveals that there is a user_transaction that is still open. How do I commit? –  EWizard May 9 '11 at 13:35
    
I ended up getting the transaction to complete by executing a COMMIT TRANSACTION in the same window of the stored procedure that showed successful completion. Didn't even know you could do that. –  EWizard May 9 '11 at 14:24
    
Yup, you can manually start and end a transaction from an SSMS query widow, since each one is associated with a single process for the life of that connection. –  db2 May 9 '11 at 16:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.