Cannot kill process with EC wait type

Question

Cannot kill process with EC wait type

b.ozga

SSCommitted

Points: 1891
More actions
March 14, 2008 at 7:10 am

#69620

Hi,
I had tired to create index on large databse (>200GB, engine ss2k) but
I got errors, so I executed DBCC CHECKDB. DBCC told me that I have to
execute it with repair options. But I noticed in Activity Monitor that
I had still running creating index process with EC wait type. Then I
started to kill it but without success.
Is it possible to kill such process, without restarting sql server?
Regards,
Bartek

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You need to take some time for your transactions rolling back. Even though you restart your SQL Server services, you still need to wait for the rolling back.

b.ozga SSCommitted Points: 1891 More actions · Answer 2

Thank you for reply, Oracle.

It seems this rollback take some time, is it possible to estimate how long it will last?

Regards,

Bartek

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

KILL 54 WITH STATUSONLY

gives

--This is the progress report.

spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

although it tends to stick at 100% for a long time

b.ozga SSCommitted Points: 1891 More actions · Answer 4

It is really usefull command. Thanks Jo.

But you was right it is stick to 100% for a long time.

SanjayAttray SSChampion Points: 13157 More actions · Answer 5

Only option to this is restart MSSQLSERVER services.

SQL DBA.

b.ozga SSCommitted Points: 1891 More actions · Answer 6

Thank you $sanjayattray, and tahnks for all post.

I did it yesterday. It seems that restarting SQL Server is the only opition to get rid of such process.

Cheers,

Bartek

tosscrosby SSCoach Points: 18045 More actions · Answer 7

See SQL ORACLE's comment about stopping MSSQL and rollback occurring. It's still going to happen. You're simply delaying the process.

-- You can't be late until you show up.

b.ozga SSCommitted Points: 1891 More actions · Answer 8

I am not questioning what SQL ORACLE wrote but at my playground it works. I cannot see that process in activty monitor, sp_who etc. and I did whatever I want with db.

Cheers,

B.

tosscrosby SSCoach Points: 18045 More actions · Answer 9

So on a restart, you didn't see a continuation of the rollback prior to the database being brought on line? That would be very odd, possible I guess, but strange. From a data integrity standpoint, does everything look right in the database? Did (or can) you run a DBCC CHECKDB and have it return without errors?

-- You can't be late until you show up.

b.ozga SSCommitted Points: 1891 More actions · Answer 10

Yes, I did DBCC CHECKDB, and succeeded without errors.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 11

Sometimes, after killing a process, we can see rollback. It stays there forever.

I came across the situation below.

I killed a LiteSpeed backup. The status of the process stayed in rollback forever because LiteSpeed opened another thread on OS level. SQL SQL can close an unused thread using its commands but not the thread on OS level. The only approach at this moment is to restart the SQL Server to close the thread.