Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Killed/Rollback process hogging ALL CPU resources.

    I have a test database for the end users to test their select queries for reports.
    One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.

    I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?

    Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?


    thanks
    nina

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What a good time to talk about execute only authorit to stored procedures...

    Your rool back can take up 2 twice as long as the original process...maybe longer...

    I doubt it was select only...any chance a work table was involved with millions of rows and they did a delete to clear it out?

    Guess you don't have the opportunity to do a code review....

    If you stop and restart the server, it'll just pick up from where it left off.

    What version is this?

    Is this a dev or production box?

    I know I saw someone once who discussed this...but it was messy

    Before you issue a kill, you should find out what the spid was doing...did you do sp_who to see how much I/O and CPU it was using?

    Do you monitor the developers with profiler?

    What login Id did the developer login with?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    10
    Hello Brett
    thanks for responding. This is a development box and that is probably the only good thing about this entire mess.
    And no i killed the process without actually looking into the query that it was running. It is SQL Server 2000 box and the user has a SQL Server account and he uses query analyzer to write/test his queries.
    The user has create view rights and belongs to db_datareader role for just the one test database on the server.
    Would a query running into an infinite loop cause this problem ?
    Before killing the process it was using about 70% of CPU but it kept hogging more resources through the night after i killed it and this morning everything on the server came to a standstill as it hogged 99% of CPU

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Put the user in the pillory, until the rollback is complete. They should learn after that ;-)

  5. #5
    Join Date
    Apr 2004
    Posts
    10
    The rollback ran through the night and ate up all our server resources and still did not complete. I just went in and restarted the server. Since this is a development environment it was not that much of a problem.

    What i would like to know is that was restarting the SQLServer the only option that we have in such a situation ? And also would a select query every cause a rollback ?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My guess is that if the restart worked then it wasn't rolling back...

    Did you check and see if to spids where deadlocked?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Posts
    10
    Yes i did check for deadlocks and there were none in the system.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, Try this next time

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    That will throw everyone out without having to issue a kill

    btw did you kill all spids?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Posts
    10
    Thanks will keep the Alter statement for future reference. And yes i did try to kill all the processes accociated with that user. And since there were only 4-5 processes for that user i know i got them all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •