How to kill a process in sql server

Question

How to kill a process in sql server

lavien

SSC Rookie

Points: 46
More actions
October 25, 2010 at 2:38 am

#240135

Hi all,
I have a table with full text catalog and some query on using this full text catalog was running so long(some days). I killed them then they change status to Kill/rollback and still running. So a can not drop full text catalog or backup database..
pls help me!
thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

A process that is killed and is doing a rollback cannot be killed again. You’ll have to wait until it will finish the rollback. By the way, even if a transaction is rolled back, you can still backup your database.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Wait or restart the SQL instance. If you restart, the rollback will continue after the restart, no way to tell how long it'll take.

Is the rollback perhaps blocked by something? Check sys.dm_exec_requests.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

*nm*

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lavien SSC Rookie Points: 46 More actions · Answer 4

lavien

SSC Rookie

Points: 46

October 25, 2010 at 5:07 am

#1241487

so i can do nothing.

thanks for help.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

You can restart the instance. If the rollback is hung that usually helps. Probably best to do out of business hours in case it takes time to come back.

Have you checked whether or not the rollback is blocked?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 6

GilaMonster (10/25/2010)
You can restart the instance. If the rollback is hung that usually helps.

I can definitely attest to Gail's comment here. But I don't advise restarting until you've exhausted all other options (including the waiting option). Or unless you're sure the rollback is blocked.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

lavien SSC Rookie Points: 46 More actions · Answer 7

There are nothing to lock. It's just a select statement and nothing have to rollback. The CPU is always 70 -80%. I try to back up data(make duplicate data). I will not restart until i have a backup.

thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Sure that select's not part of a longer transaction that does have stuff to rollback?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lavien SSC Rookie Points: 46 More actions · Answer 9

I don't know why it has to rollback. The server have more than 30 process that the select statement using the full text catalog for some days(I had been DBA of that server in a few days). I killed them all but it's change to Killed/Rollback status.

Now i try to duplicate each objects in that db to another for backup. do you have some suggestion?

thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

What's wrong with just taking a backup?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lavien SSC Rookie Points: 46 More actions · Answer 11

lavien

SSC Rookie

Points: 46

October 26, 2010 at 4:24 am

#1242127

I cannot back up. it take a day without error.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

Doesn't make any sense. What's blocking the backup?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lavien SSC Rookie Points: 46 More actions · Answer 13

lavien

SSC Rookie

Points: 46

October 26, 2010 at 5:04 am

#1242143

hix.., i can not know why.

thanks for your help

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 14

You really need to find out why the backup is being blocked. Check your error logs, check the default trace, run a server side trace, whatever you have to. But find out what could be blocking the backup. If you don't, then you can't resolve the problem.

And for the record, a "simple" select can take forever to rollback if you're pulling large chunks of data from a table that is both wide and long. I've seen it happen in my workplace. Especially if you've thrown a few joins into the mix.

EDIT: What's the size of your database? What's the recovery mode? Why type of backup takes a day?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.