Memory used

I've got this problem: I execute a bulk insert via a
stored procedure of about 500000 records. The bulk insert
works fine, but the memory used grows up very fast. Then,
it never goes down.
So if I try to do another bulk insert via the same stored
procedure, the sp doesn't work and the statement have been
terminated without any error.
The server is a pentium 4 (single processor) 1,5 Ghz and 1
Gb of memory, with windows 2000 server service pack 3 and
it is used for development purposes.
Thank you.marco
Are you sure there are no other activites on the server at the same time?
Have you looked at Profiler ?
"marco" <angilerm@.lloydadriatico.it> wrote in message
news:21e701c3a925$4cad3650$a601280a@.phx.gbl...
> I've got this problem: I execute a bulk insert via a
> stored procedure of about 500000 records. The bulk insert
> works fine, but the memory used grows up very fast. Then,
> it never goes down.
> So if I try to do another bulk insert via the same stored
> procedure, the sp doesn't work and the statement have been
> terminated without any error.
> The server is a pentium 4 (single processor) 1,5 Ghz and 1
> Gb of memory, with windows 2000 server service pack 3 and
> it is used for development purposes.
> Thank you.sql

Memory Leaks using MSDE 2000

Hello.
Our company is running an application that uses the MSDE 2000 (SP4), to load
large amount of data using bulk inserts.
The sqlservr.exe process increases from the 4-10MB that it starts with to
80-100MB after all transactions has been completed.
When starting a new transaction session, the process just continue to grow
causing the OS to perform poorly.
Ok, I understand that while on an insert transaction, the memory should grow
but when we are done, why doesn’t it release the resources?
You can see that this is a bad behavior if you just close the process
manually and start it again, the memory will start with 4-10MB, and the
application still works, meaning that the exe have lots of unused resources.
What can I do about that (other than limiting the memory size to xMB)?
Hi
Not leaking, it is by design
INF: SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
> Hello.
> Our company is running an application that uses the MSDE 2000 (SP4), to
> load
> large amount of data using bulk inserts.
> The sqlservr.exe process increases from the 4-10MB that it starts with to
> 80-100MB after all transactions has been completed.
> When starting a new transaction session, the process just continue to grow
> causing the OS to perform poorly.
> Ok, I understand that while on an insert transaction, the memory should
> grow
> but when we are done, why doesn't it release the resources?
> You can see that this is a bad behavior if you just close the process
> manually and start it again, the memory will start with 4-10MB, and the
> application still works, meaning that the exe have lots of unused
> resources.
> What can I do about that (other than limiting the memory size to xMB)?
>
>
|||Ok, it’s by design, pretty weird design, by ok.
Now, how can I handle this, design or not, it is a problem…
Limiting the max memory? Stopping and starring the process of the SQL?
What is the best practice for it?
Eli.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Not leaking, it is by design
> INF: SQL Server Memory Usage
> http://support.microsoft.com/default...b;en-us;321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Eli" <Eli@.discussions.microsoft.com> wrote in message
> news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
>
>
|||Hi
Set the MAX memory.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:5D23EFC5-6240-47E3-AF83-B8B0CB0DC5D7@.microsoft.com...[vbcol=seagreen]
> Ok, it's by design, pretty weird design, by ok.
> Now, how can I handle this, design or not, it is a problem.
> Limiting the max memory? Stopping and starring the process of the SQL?
> What is the best practice for it?
> Eli.
>
> "Mike Epprecht (SQL MVP)" wrote: