Begin Tran w/o Commit Tran error-- now table wont delete - what to

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by Umlja » Wed, 19 Mar 2008 01:04:00


Sql Server 2000

I was running a test on an SP with a Begin Transaction to insert 2 rows into
a table from another table which only contains 2 rows. But I forgot to
include a commit transaction (actually there was a commit tran... about 100
lines down but I commented it out and forgot to include it in the test
portion of the SP). Now I can't delete the table or even drop it -- from
query analyzer. The little world just keeps spinning and spinning

What should I do? Should I stop the server and restart it? What to do?
The little world icon is still just spinning around.

Thanks,
Rich
 
 
 

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by TheSQLGur » Wed, 19 Mar 2008 01:15:46

Kill the spid (see the kill command in BOL) and the tran should rollback and
then you can delete.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

 
 
 

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by Umlja » Wed, 19 Mar 2008 01:21:03

I took a chance and stopped the server and restarted it. That appears to
have fixed the problem. Is there something simpler I could have tried?
 
 
 

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by dave balla » Wed, 19 Mar 2008 01:23:49


just "ROLLBACK" from the session that inserted the data, im gussing
that the delete is beign issued in another session and is blocked..


Dave
 
 
 

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by Alex Kuzne » Wed, 19 Mar 2008 02:21:12


run sp_who2
to figure out spid of the blocking connection. Kill it.
 
 
 

Begin Tran w/o Commit Tran error-- now table wont delete - what to

Post by Umlja » Wed, 19 Mar 2008 03:57:00

Thank you all for your replies. The methods suggested seem better than just
restarting the sql server.