title: How can I avoid BCP T-SQL Bulk Insert Timeout?
body: <p>I am looking to deploy 124,978,454 rows of data to a production server.  After some research and asking around I arrived at using the bcp.exe to create a .dat file and then use T-SQL Bulk Insert to the destination table.  On the destination table I am disabling foreign keys as well as the non-clustered index on that table (but leaving the PK in tact).  <strong>I am using SQL Server 2008 R2</strong>.</p>
  
  <p>I am using our dev environment to get an idea of how long I should expect the process to take.  So far, what I've done is:</p>
  
  <p>1) Create the .dat file using bcp with the following command line code (I've anonymized it)</p>
  
  <pre><code>bcp "select * from MyDatabase.dbo.MyTable" queryout "c:\data.dat" -n -T -S Servername
  </code></pre>
  
  <p>2) Using the following T-SQL I am trying to bulk insert data</p>
  
  <pre><code>USE master;
  ALTER DATABASE MYDATABASE SET RECOVERY BULK_LOGGED;
  
  Use MYDATABASE 
  GO
  
  --Disabled indexes and foreign keys
  ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
  
  ALTER INDEX IDX_MyTable_ReportViewID ON MyTable DISABLE;
  
  BULK INSERT MyTable
      FROM 'C:\data.dat' 
     WITH (DATAFILETYPE='native', BATCHSIZE = 50000); 
  GO
  
  USE master;
  ALTER DATABASE MYDATABASE SET RECOVERY FULL;
  </code></pre>
  
  <p>This kicks off without a problem.  I let it run overnight but came back to the machine this morning to get a <strong>timeout expired</strong> error.</p>
  
  <p>The error message is this:</p>
  
  <pre><code>TITLE: Microsoft SQL Server Management Studio
  ------------------------------
  
  The Activity Monitor is unable to execute queries against server SERVERNAME.
  Activity Monitor for this instance will be placed into a paused state.
  Use the context menu in the overview pane to resume the Activity Monitor.
  
  ------------------------------
  ADDITIONAL INFORMATION:
  
  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
  
  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;ProdVer=10.50.2550&amp;EvtSrc=MSSQLServer&amp;EvtID=-2&amp;LinkId=20476
  
  ------------------------------
  </code></pre>
  
  <p>I have no idea when this error occurred as no datetime was given.  I need an estimate of how long this data transfer will take so I can plan for our deployment to production.</p>
  
  <p>My research has turned up solutions for performing this task in C# which is not applicable to me.</p>
  
  <h2>My question: How can I avoid this timeout error?</h2>
  
  <p>Alternatively, if there are options better than using the bcp utility I am willing to investigate those.</p>