I am working on a project which processes big data (size ~3TB) every day. The first stage of our data pipeline copies the data from our MS-SQL Server to a host machine(linux server) using a tool called freebcp. More info on freebcp is here Using this tool from the linux server, we run a set of stored procedures on SQL Server and export the data and transfer it in bulk. Recently I am observing that if the data is huge (~200GB), the data transfer is getting stalled after sometime. I ran a couple of commands (sp_who2, and dbcc inputbuffer(spid)) to monitor the execution of stored procedure on SQL server. We observe the CPU time and Disk IO utilized by this procedure. If this is not changing for a few minutes, we assume the job is stalled, and manually kill these stored procedures to continue our data processing tasks. What are the probable reasons for this stalling of data copy?Is there any better way to copy the data in bulk from SQL server to linux host? May be an alternative to freebcp. After this, we load this data into hadoop file system run our map reduce tasks.
freebcp getting stalled for huge data
bigdata;apache hadoop