Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

killing a large bcp Expand / Collapse
Author
Message
Posted Monday, March 26, 2007 9:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:56 AM
Points: 115, Visits: 145

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

 

Post #353899
Posted Monday, March 26, 2007 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:13 AM
Points: 1,295, Visits: 796

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
Post #353911
Posted Monday, March 26, 2007 11:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:56 AM
Points: 115, Visits: 145

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

 

Post #353931
Posted Tuesday, March 27, 2007 2:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 26, 2010 10:24 AM
Points: 190, 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..

Post #354077
Posted Tuesday, March 27, 2007 3:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:56 AM
Points: 115, Visits: 145

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

 

 

Post #354090
Posted Tuesday, March 27, 2007 4:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 8, 2014 3:17 AM
Points: 246, Visits: 384

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.

Post #354101
Posted Tuesday, March 27, 2007 4:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 8, 2014 3:17 AM
Points: 246, Visits: 384

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.

Post #354102
Posted Tuesday, March 27, 2007 4:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:56 AM
Points: 115, Visits: 145

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

Thanks Todd

Post #354105
Posted Saturday, November 12, 2011 1:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 12:19 AM
Points: 6, Visits: 269
Thanks, you solved my problem.
Post #1204565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse