0

I have a large table without any indexes on it, about 27.5m rows. I tried to delete about half of those using using BEGIN TRAN. I then tried canceling the querie, but because it was taking long I decided to just close the Management Studio.
Now when I try to look into that table, it does not return anything, just keeps running.
When I run SELECT TOP 10 * FROM Tbl it just executes without returning anything.
But when I run SELECT TOP 10 * FROM Tbl(NOLOCK) it return 10 rows.
This tells me that it is waiting on a Rollback/Commit from my BEGIN TRAN.
I thought it would rollback automatically after closing the session and Management Studio.
How do I fix this issue?

Thanks.

jmich738
  • 1,565
  • 3
  • 24
  • 41
  • https://stackoverflow.com/questions/4896479/what-happens-if-you-dont-commit-transaction-in-a-database-say-sql-server – ProgrammingBaKa Jul 04 '17 at 03:26
  • maybe you should close your connection to the database? – ProgrammingBaKa Jul 04 '17 at 03:26
  • @ProgrammingBaKa I've read that and it says that it should have rolled it back once I close the session. I have closed it but it still appears locked. How do I close connection to DB? – jmich738 Jul 04 '17 at 03:28
  • I would say try closing the connection as well but I thought If using Management studio the connection should be terminated/closed by closing the query window. – OLIVER.KOO Jul 04 '17 at 03:29
  • I did that. Closed all of the Management Studio instance I had opened. Still only works while NOLOCK is present. – jmich738 Jul 04 '17 at 03:34
  • 1
    To close connection have you try terminating sqlserver.exe from Task Manager or activity monitor if you have a mac? – OLIVER.KOO Jul 04 '17 at 03:34
  • Ending Tasks from the Task Manager worked. – jmich738 Jul 04 '17 at 03:40
  • Great. I will post it as an answer so people who run into the same problem can find the solution easier. – OLIVER.KOO Jul 04 '17 at 03:44

2 Answers2

1

Execute SP: SP_LOCK

In Results you will get SPID, DBID, OBJID, INDID, TYPE, RESOURCE, MODE, STATUS
Now check the status column, if it is showing wait then

kill that SPID. To kill a particular SPID Execute SP: Kill 65 (Where 65 is SPID)

MSDN Forum

Sushil Mate
  • 583
  • 6
  • 14
0

Apparently, this worked:

Terminate/Close connection to the database would solve this. To close connection you can try terminating sqlserver.exe from Task Manager or Activity Monitor if you have a Mac.

OLIVER.KOO
  • 5,654
  • 3
  • 30
  • 62