BCP takes lot's of memory and doesn't release it

BCP takes lot's of memory and doesn't release it

Post by Rene Balver » Fri, 24 Dec 1999 04:00:00

I'm performing a bulkcopy of 400.000 rows, and while the BCP (BULK INSERT)
is running the amount of memory used by SQL Server increases from 90 MB to
215 Mb. The ROWS_PER_BATCH is set to 10.000, but I just read the option
ROWS_PER_BATCH doesn't work if the BATCHSIZE parameter is not specified.

There is an index on the table, and the log was getting full, but that is
fixed now. But why doesn't release SQL Server the memory? The PC has 256Mb,
which should be enough, but the performance is not to good when 215 Mb is
used by SQL Server.

Even if all clients are disconnected, the memory used by SQL Server stays
215 Mb. Restarting the server will bring back a good performance.

Did anyone had the same problems or know a solution?

Kind regards and a Merry Christmas,

Rene Balvert


BCP takes lot's of memory and doesn't release it

Post by Umachandar Jayachandra » Fri, 24 Dec 1999 04:00:00

    Do you have SP1 applied? If not, i believe that there is a memory leak
when you use the BULK INSERT statement in the RELEASE version of SQL Server.
You will have to apply SP1 for fixing this problem. Check the SP1 fixlist
for the details.

Umachandar Jayachandran
MCDBA, MCSE + Internet, MCP + Internet, MCSE


BCP takes lot's of memory and doesn't release it

Post by Neil Pik » Fri, 24 Dec 1999 04:00:00

Rene - contact MS PSS for hot-fix build 747 or above

Q.     What are SQL Server hot-fixes and where can I get them?
(v1.3  1999.04.11)

A. Hot-fixes are builds of SQL Server just like service-packs.  However they
are not fully packaged or given the same level of testing as a servicepack.  
They should only be applied to resolve a particular problem.

To get them, the official answer is that you must contact Microsoft Product
Support Services by phone.  You can get contact details from
http://support.microsoft.com/support/supportnet/default.asp.  The call screener
answering the phone will charge your account/credit card for the call - in the
US this is $195 I believe.  Then when the engineer you are put through to
determines the call is about an MS bug they will refund it.  They will tell you
where to get the hot-fix, or will send it to you.

Unofficially, you can pick up hotfixes from
ftp.microsoft.com/bussys/sql/transfer as long as you know the build number and
pkzip password you need.  The files are called SQLbbbm.EXE where bbb is the
build number and m is the machine type.  So SQL324I.EXE is build 324 for Intel.
 The files are pkzip password protected.

Wherever you get one from be aware that hot-fixes are typically NOT regression
tested and can cause a lot more damage than they can fix.  You are strongly
urged to discuss using any hot-fix with an MS PSS representative before
applying it.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (391 entries) see

 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk


1. SQL Server takes up system RAM and doesn't want to release


I am running SQL 7.0's with sp2 and sp3.  For some
reasons, everytime I run and T-SQL command or run Front-
end application or try to connect to SQL Server, SQL
Server seems to consume more RAM and doesn't release RAM
back to the system.  

My Server 's running 512MB of RAM.  Around 2 or 3 days
when SQL Server takes all the System RAM, users will get
time out error and the only solution now for me is to
reboot the server, then it will last for another 2 or days

I use Task Manager to monitor sqlsevr.exe process and look
like this process keep holding RAM more and more until it
reachs the max.

Anyone experience the same problem as mine?  Thanks a lot
for any help.

Ps: I plan to upgrade memory, but that still not really an
absolute solution in this case.


2. UV 9311E on NT: UV Session appears to hang after ^C

3. SQL Server doesn't release memory

4. Request for Clue - Paradox->Oracle Port

5. CT-Danbury-265574--Client/Server-ORACLE-SQLSERVER-Senior Application Specialist

6. Scheduling DTS doesn't fail but doesn't work

7. a newbie desperately need help on oracle server installation

8. class doesn't support automation or doesn't support expected interface


10. class doesn't support automation or doesn't support expected interface

11. sql server taking up a lot of memory

12. JVIEW taking up lots of memory