Post reply

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

  • 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

  • October 8, 2004 at 2:36 pm

    #526023

    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.

     

  • October 8, 2004 at 2:44 pm

    #526026

    Hi,

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

     

    Thank you

  • October 8, 2004 at 3:09 pm

    #526030

    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.

     

  • October 8, 2004 at 3:40 pm

    #526032

    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.

    .

  • October 11, 2004 at 6:52 am

    #526133

    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

  • October 11, 2004 at 7:55 am

    #526150

    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.

     

     

  • October 11, 2004 at 8:00 am

    #526166

    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.

  • October 14, 2004 at 12:00 pm

    #526806

    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.

  • October 14, 2004 at 1:10 pm

    #526829

    I am glad it worked for you.

     

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

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

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