URGENT NEED HELP KILL/ROLLBACK PROCESS KEEPS RUNNING AND EATING CPU

Question

Post reply

URGENT NEED HELP KILL/ROLLBACK PROCESS KEEPS RUNNING AND EATING CPU

Dave-175556

SSC Eights!

Points: 896
More actions
October 8, 2004 at 2:20 pm

#84222

HI,
I have sql server 2000 with sp 3 on windows 2000 server. Couple of days back i got problem that some bulk inserts and other two dts processes were running and eating up cpu usage. so yesterday i had to kill those. So currently i have total 5 processes kill/rollback status. Processes 88, 89, 84 are eating lot of cpu but physical_io is 0 for them. they are running on dbid 8. when i run DBCC OPENTRAN on dbid 8, i get the process id 86. But 86 has status waiting and it's cpu and physical_io doesn't change. All these processes are in KILL\ROLLBACK status.
Need help immediately.

Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

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

Don Millhouse SSC Eights! Points: 977 More actions · Answer 1

When I run into that situation I reboot the server.

When i restart I start SQL in single user mode and let the databases recover then restart in normal mode.

Dave-175556 SSC Eights! Points: 896 More actions · Answer 2

Hi,

What are the chances of database getting corrupted in this situation or going to suspect mode if i reboot the server?

Thank you

Don Millhouse SSC Eights! Points: 977 More actions · Answer 3

This fix is at your own risk. I have never had a database come back suspect or corrupt becuase of a reboot.

I have however had to wait a couple of hours once for the database recovery. Usually the recovery is very quick though.

I am just sharing what my method is. I have done this twice this year, so it is not a common occurrance.

mybid2003 SSCertifiable Points: 6620 More actions · Answer 4

I agree with you Don. I did this morethan few times this year in SQL Server 6.5, twice in SQL 2000 SP3 this year.

Again its just sharing and it is at your own risk.

.

hildevan Say Hey Kid Points: 691 More actions · Answer 5

Hi

I sharing too. The risk we knows.

I had many problems with kill sessions and the last resource was a reboot or stop and start SQL Server.

The chance of you corrupt database use this processes is very difficult. You need understand a processes , if it use a temp tables.

In best pratice programming, all developers design system prevent this situations.

The risk is own and know....

Hildevan O Bezerra

Peter Wright SSCommitted Points: 1966 More actions · Answer 6

Well the processes may have rolled back by now, 3 days on from the posting. If not then I think you will have to stop & start the SQL Server Service. On restart the db will go into recovery mode but it should be OK several minutes later. Back the database up before the reboot and schedule a dbcc checkdb & possibly dbreindex for a quiet time afterwards.

Your description of the symptoms - no I/O but lots of CPU - indicate the processes are just 'spinning' and not writing data so I think the recovery will be quick.

This has always worked for me but, as other posters say, it's rare.

iLoveSQL SSCrazy Points: 2888 More actions · Answer 7

Just one little tip, Use "xp_readerrorlog" from SQL Analyzer to see how far got for rolling back not from EM if you ever decide to reboot.

Dave-175556 SSC Eights! Points: 896 More actions · Answer 8

Thank you all. Reboot took care of everything. It took couple of hours to recover databases as those were real big ones but everything seems fine now. Thank you.

Don Millhouse SSC Eights! Points: 977 More actions · Answer 9

I am glad it worked for you.

Every DBA has a little anxiety now and then....