1

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.

MadHatter
  • 78,442
  • 20
  • 178
  • 229
EWizard
  • 11
  • 2

1 Answers1

1

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.

db2
  • 2,170
  • 2
  • 15
  • 19
  • Process has completed. How can I manually commit? – EWizard May 09 '11 at 13:11
  • The stored procedure was run in SSMS manually. – EWizard May 09 '11 at 13:32
  • DBCC OPENTRAN reveals that there is a user_transaction that is still open. How do I commit? – EWizard May 09 '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 09 '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 09 '11 at 16:25