BAD Locking problem
-
I have three processes which are causing me a bad headache.
SPID 56 was a create index on a table (DBID 8) with around 8.5million rows . This got an IX TAB lock on SysIndexes.
56 Was killed causing a rollback(?)
SPID 124 was a view alter. The view is in a different database (19) but referencing a table in DBID 8.
This was also killed. The rollback states that it's 100% complete, 0 seconds remaining yet nothing happens.
There is a Autoshrink on SPID 20 which is blocked by 124 (Which is waiting for 56)
56 says 0% complete, 1 second remaining.
All this has been like this for around an hour now.
Is all I can do wait for 56 to complete?
The index build was on a INT column so not to large.
The IX TAB lock on SysIndex is causing the entire DB to appear "broken" (Couple of choice words come to mind)
Cheers,
Crispin
ps: Non of this was my fault
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
To know the remaining time, execute kill spid. This will put the process in killed/rollback and will tell you the progress of the rollback.
If it is 100 % and o seconds remaining maybe you will have to restart the server. It happens to me sometimes and the only way I found was to restart the server.
-
What was confusing me was that when I did do that, it still said "0% complete, 1 Second remaining" Even after 8 hours. When I checked from home, it had completed. No time estimation was given.
I would have to advise against rebooting. That has happened to me on one occasion and all it did was cause the rollback to take longer. Ended up completing 24 odd hours later.
Just checked the DB and it's finally complete. Some 9 hours later. I must say though this was not a nice experience. A lock on SysIndexes on a production box which caused an entire day of downtime. Not nice.
Question I have though:
The original create index ran for a couple seconds then took a couple hours to roll back. Why?
The machine was also relaxed during the rollback. All procs below 20% (Most of the time) Disks were quite.
Why would SQL not ask more of the machine to undo what it has done.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply