killing a large bcp

Question

killing a large bcp

EamonSQL

SSCrazy

Points: 2307
More actions
March 26, 2007 at 9:54 am

#116762

Hi,
I kicked off a bcp that will dump over 20 millions records and needed to kill the process.
Howevere this takes forvere to stop.
Is there a quick way of doing this?
I am using xp_cmdshell 'bcp....out........' to dump the data.
Any help welcome.
Thanks in advance,
Eamon

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Eamon,

You can check the current status through Enterprise Manager (Your server -> Management -> Current Activity -> Process Info).

It will show the details of the SPID (Process ID), Database, Status, Command etc. Choose the appropriate SPID and Right Mouse Click and select "Kill Process".

You can also do from TSQL

Find out the SPID and then execute KILL <SPID>

Hope this helps.

Thanks

Lucky

EamonSQL SSCrazy Points: 2307 More actions · Answer 2

When i kill the process SQL goes into a roll back.

Does this mean that the rows inserted so far from the text file I am importing from will be removed leaving the destination table back in its original state?

Cheers for your help on this.

Eamon

Patrick Folan Mr or Mrs. 500 Points: 512 More actions · Answer 3

Eamon,

Yes, it is rolling back the transaction. Check if the table is indexed......the rollback will take much longer if so..

If the job is running over a network rather than locally it will take much longer as well..

EamonSQL SSCrazy Points: 2307 More actions · Answer 4

Thanks,

so if I have been importing records using bcp then cancelled this then those records should then not appear after it eventually rollbacks?

An obvious question I know !

Regards,

Mr Careful

Todd Townley Ten Centuries Points: 1320 More actions · Answer 5

I recently ran into the same problem. Once I figured it out, though, the answer was obvious.

First, the BCP utility has a batch parameter that allows you to specify the number of records to group in a transaction. (We often use 50,000.) BCP will commit this many records at a time. If something happens during the BCP process, only the last batch will be rolled back. If this parameter is not used, by default the entire dataset becomes one batch.

Second, it’s important to remember that the BCP utility is being run in a command shell. It is actually running as a separate process. If you look at Task Manager while the import is running, you will see a process called BCP.exe. This is the actual import process. The SQL Server process is simply waiting for BCP.exe to complete.

So if you cancel/kill only the SQL Server process, that process will wait until BCP.exe completes (at which point the import is finished) before the SQL Server process will die. You must also end the BCP.exe process using Task Manager to get the import to stop.

Note that stopping the BCP.exe process will roll back the last batch. If no batch parameter was specified, this should roll back everything.

Todd Townley Ten Centuries Points: 1320 More actions · Answer 6

I recently ran into the same problem. Once I figured it out, though, the answer was obvious.

First, the BCP utility has a batch parameter that allows you to specify the number of records to group in a transaction. (We often use 50,000.) BCP will commit this many records at a time. If something happens during the BCP process, only the last batch will be rolled back. If this parameter is not used, by default the entire dataset becomes one batch.

Second, it’s important to remember that the BCP utility is being run in a command shell. It is actually running as a separate process. If you look at Task Manager while the import is running, you will see a process called BCP.exe. This is the actual import process. The SQL Server process is simply waiting for BCP.exe to complete.

So if you cancel/kill only the SQL Server process, that process will wait until BCP.exe completes (at which point the import is finished) before the SQL Server process will die. You must also end the BCP.exe process using Task Manager to get the import to stop.

Note that stopping the BCP.exe process will roll back the last batch. If no batch parameter was specified, this should roll back everything.

EamonSQL SSCrazy Points: 2307 More actions · Answer 7

that's VERY VERY useful and puts my problem in total context.

Thanks Todd

ali_a_asgari Old Hand Points: 338 More actions · Answer 8

ali_a_asgari

Old Hand

Points: 338

November 12, 2011 at 1:29 am

#1406650

Thanks, you solved my problem.