Error: 701 Insufficient Memory - Nightly Data Update

We are running a relatively small data warehouse on a 2 CPU Dell server w/ 4
GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other load
on this server is IIS running our reporting application.
Our nightly data updates are a collection of relatively simple stored
procedures that extract data from DB2 on an AS/400 into the DW in a series of
stages. We're moving approximately 1 GB of data each night using the job
scheduler in SQL Server agent.
These nightly jobs fail at various steps with the following error:
"Executed as user: sa. There is insufficient system memory to run this
query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed."
I am eventually able to get each job to complete by restarting SQL Server
and SQL Server Agent after failure, and then restarting the jobs from the
last failed step. This is obviously an unacceptable solution.
I've read some of the other posts on similar problems, but none of the
solutions provided have been applicable to our problem.
This appears to be a memory leak. We have increased the memory available to
SQL Server as well as the virtual memory. These steps have simply delayed the
problem so that it occurs later in the process.
Can anyone provide any insights or guidance on how to address this problem?
--
Thanks, KenFirst hopefully you have enabled AWE, are running SQL Enterprise Ed. and
have applied the memory hotfix. But none of that would help directly with
error 1750 if it is the root cause.
Second hopefully, you don't have all your data files on the same disk on a
RAID 5 array. If you do, watch your CPUs when your loading. They will go
high and stay there if you are waiting on the disk. If you are waiting on
the disk, the DB2 connection could be trying to push when your system
doesn't have the resources.
some thoughts that might offer some leads.
d.
"Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> We are running a relatively small data warehouse on a 2 CPU Dell server w/
4
> GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
load
> on this server is IIS running our reporting application.
> Our nightly data updates are a collection of relatively simple stored
> procedures that extract data from DB2 on an AS/400 into the DW in a series
of
> stages. We're moving approximately 1 GB of data each night using the job
> scheduler in SQL Server agent.
> These nightly jobs fail at various steps with the following error:
> "Executed as user: sa. There is insufficient system memory to run this
> query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> I am eventually able to get each job to complete by restarting SQL Server
> and SQL Server Agent after failure, and then restarting the jobs from the
> last failed step. This is obviously an unacceptable solution.
> I've read some of the other posts on similar problems, but none of the
> solutions provided have been applicable to our problem.
> This appears to be a memory leak. We have increased the memory available
to
> SQL Server as well as the virtual memory. These steps have simply delayed
the
> problem so that it occurs later in the process.
> Can anyone provide any insights or guidance on how to address this
problem?
> --
> Thanks, Ken|||Thanks D,
My problem is still not solved, but I thought I would give an update for
anyone else experiencing similar problems.
We are running SQL 2000 Enterprise Edition SP4; we have AWE enabled; data
files spread across the RAID array; and I did install the hotfix described in
the article: http://support.microsoft.com/kb/899761. In fact I installed a
cumulative hotfix that includes all hotfixes for SP4
(http://www.microsoft.com/downloads/details.aspx?familyid=4773BF7E-21AE-4F1E-AD48-6CA739E10217&displaylang=en).
I did discover that we had set 'Max Degree of Parallelism' to 1, which means
we are only using 1 of the 2 processors available. It turns out that prior to
the 701 "Insufficient Memory" error, our jobs were failing with the error:
"The query processor could not start the necessary thread resources for
parallel query execution. [SQLSTATE 42000] (Error 8642)."
We circumvented this problem by using only 1 CPU. I would love to know if
anyone else has handled this error before. I can't find anything on Technet
or other SQL Server forums relating to this error.
I currently have a case opened with MSFT support. They have directed me to
disable AWE since it is not needed for only 4GB RAM. I've run several MSFT
analysis, profiling, and perf monitoring tools which have so far not
identified the root cause of the problem.
One thing I recently realized is that we are running Windows 2003 Standard
edition, which has more memory limitations than Enterprise edition. I'll be
researching this more tomorrow. Meanwhile, if any experts can shed light on
how this may relate to my problem. I would greatly appreciate your ideas.
--
Thanks, Ken
"d" wrote:
> First hopefully you have enabled AWE, are running SQL Enterprise Ed. and
> have applied the memory hotfix. But none of that would help directly with
> error 1750 if it is the root cause.
> Second hopefully, you don't have all your data files on the same disk on a
> RAID 5 array. If you do, watch your CPUs when your loading. They will go
> high and stay there if you are waiting on the disk. If you are waiting on
> the disk, the DB2 connection could be trying to push when your system
> doesn't have the resources.
> some thoughts that might offer some leads.
> d.
> "Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
> news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> > We are running a relatively small data warehouse on a 2 CPU Dell server w/
> 4
> > GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
> load
> > on this server is IIS running our reporting application.
> >
> > Our nightly data updates are a collection of relatively simple stored
> > procedures that extract data from DB2 on an AS/400 into the DW in a series
> of
> > stages. We're moving approximately 1 GB of data each night using the job
> > scheduler in SQL Server agent.
> >
> > These nightly jobs fail at various steps with the following error:
> >
> > "Executed as user: sa. There is insufficient system memory to run this
> > query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> > previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> > terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> >
> > I am eventually able to get each job to complete by restarting SQL Server
> > and SQL Server Agent after failure, and then restarting the jobs from the
> > last failed step. This is obviously an unacceptable solution.
> >
> > I've read some of the other posts on similar problems, but none of the
> > solutions provided have been applicable to our problem.
> >
> > This appears to be a memory leak. We have increased the memory available
> to
> > SQL Server as well as the virtual memory. These steps have simply delayed
> the
> > problem so that it occurs later in the process.
> >
> > Can anyone provide any insights or guidance on how to address this
> problem?
> > --
> > Thanks, Ken
>
>sql