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
ninaWhat 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?|||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|||Put the user in the pillory, until the rollback is complete. They should learn after that ;-)|||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 ?|||My guess is that if the restart worked then it wasn't rolling back...

Did you check and see if to spids where deadlocked?|||Yes i did check for deadlocks and there were none in the system.|||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?|||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.