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
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.
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.
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..
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 !
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.
that's VERY VERY useful and puts my problem in total context.