Suspended bcp
in [Programming SQL]
|
From: Dan on 3 Jun 2010 18:17 I am using SQL Server 2008 x64 and running a bcp statement to insert a large amount of data (100M rows, 50Gb). Problem is that as it runs, it gobbles up more and more ram, and then eventually locks up and the spid goes to "Suspended" Status. I read some articles about this problem in SQL 05, and it was reported that this was a bug in 05 and that you needed to run: DBCC FREEPROCCACHE to free it up. When I run that, the query goes back to runnable status for a second, and then right back to "Suspended" What can I do to get this to run? Here is my bcp stmt: bcp Conversion.dbo.MyTable in MyTable.txt -T -c -S .\sql08 -h "ROWS_PER_BATCH = 50000" -o "C:\Temp\Log_MyTable.txt
From: Erland Sommarskog on 4 Jun 2010 18:19 Dan (dantheriver(a)newsgroup.nospam) writes: > I am using SQL Server 2008 x64 and running a bcp statement to insert a > large amount of data (100M rows, 50Gb). Problem is that as it runs, it > gobbles up more and more ram, and then eventually locks up and the spid > goes to "Suspended" Status. > > I read some articles about this problem in SQL 05, and it was reported > that this was a bug in 05 and that you needed to run: DBCC FREEPROCCACHE > to free it up. When I run that, the query goes back to runnable status > for a second, and then right back to "Suspended" > > What can I do to get this to run? > > Here is my bcp stmt: > > bcp Conversion.dbo.MyTable in MyTable.txt -T -c -S .\sql08 -h > "ROWS_PER_BATCH = 50000" -o "C:\Temp\Log_MyTable.txt I'm not aware of this issue, but what happens if you change the batch size to 5000? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Remote restore Next: Fastest way to Copy DB + Keys,Constraints to another server |