Killing Procees in SQL Server 2000

Question

Post reply

Killing Procees in SQL Server 2000

Mak Mohan

SSC Eights!

Points: 994
More actions
July 10, 2009 at 6:21 am

#75502

Hi
I just altered the size of column in table using wizard.
Table is having millions of records. So from 3-4 hrs it is executing alter operation.
I want to kill this alter process.
How to kill the process and how to identify the process that is executing alter operation?
I am using SQL Server 2000. I altered size using wizard not using query.
-Abhijeet

Viewing 7 posts - 1 through 6 (of 6 total)

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Checkout sp_who2

[font="Verdana"]Thanks
Chandra Mohan[/font]

Randy-574768 Ten Centuries Points: 1385 More actions · Answer 2

NOTE: This is bound to take several hours to rollback because of the operation you are doing and how much time you are into it already.

After looking at sp_who2 and finding the SPID you think it is, you can verify with:

dbcc inputbuffer (spid)

If you're sure that's the one, then you can use

kill spid

You can use the following to check its progress:

kill spid with statusonly

Another option to altering the table, which can take forever, is to create one just like it with changes you want made, then copy the data over. After that, create the indexes. Then you can rename the old one "table_old" and the new one "table". Generally what I do is write up a script for renaming triggers, indexes and table using sp_rename.

Randy

Mak Mohan SSC Eights! Points: 994 More actions · Answer 3

Hi

I killed that process successfully.

Before that process was running for 1 hr.

Will it take same time for rollback?

How to make sure that rollback is finished or going on?

Randy-574768 Ten Centuries Points: 1385 More actions · Answer 4

Well, you could use:

kill spid with statusonly

spid = the spid you killed in the first place.

Randy

Mak Mohan SSC Eights! Points: 994 More actions · Answer 5

Mak Mohan

SSC Eights!

Points: 994

July 10, 2009 at 6:47 am

#1022985

Thanks a lot !

-Abhijeet

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Abhijeet Dighe (7/10/2009)
I just altered the size of column in table using wizard.
Table is having millions of records. So from 3-4 hrs it is executing alter operation.
I want to kill this alter process.
How to kill the process and how to identify the process that is executing alter operation?
I am using SQL Server 2000. I altered size using wizard not using query.

How many millions of records are there? What is the size the table?

I think it should not take 3-4 hrs. Did you check whether it is blocked by another process?

Next time don't use the Wizard. Use ALTER TABLE command. Because the wizard creates temporary table and copied all the rows, which takes time. ALTER TABLE directly alters the column.