SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


killing a large bcp


killing a large bcp

Author
Message
EamonSQL
EamonSQL
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 181

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


lucky-80472
lucky-80472
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 842

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
EamonSQL
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 181

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
Patrick Folan
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 202

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
EamonSQL
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 181

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
Todd Townley
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 478

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
Todd Townley
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 478

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
EamonSQL
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 181

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

Thanks Todd


ali_a_asgari
ali_a_asgari
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 269
Thanks, you solved my problem.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search