Scheduled job fails with 'EXCEPTION - Insufficient memory'

[Derek]

A series of export/import jobs are scheduled on a dozen databases sitting on one of our servers, and are run at regular intervals through the day. Some of the jobs are failing with the following error recorded in the 'View Job History..':

EXCEPTION: Insufficient memory for this operation. Process Exit Code 2. The step failed.

Will this be cured by increasing the memory available to SQL Server (it has 512Mb already, 1/2 of the total physical RAM)? Also, why are only some jobs failing and others completing? Should I run performance monitor when the next schedule is?

Thanks

Derek

[Satya SK Jayanty]

By default, SQL Server can change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647.

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. SQL Server grows or shrinks the buffer cache to keep free physical memory at 5 MB plus or minus

I presume you can set perfmon/profiler to the job which is getting failed with that error. Also try by setting more virtual memory at NT box which gives better performance to memory allocation.

Hope this helps.
Cheers
--Satya

------------
Derek at 5/31/01 7:56:42 AM

A series of export/import jobs are scheduled on a dozen databases sitting on one of our servers, and are run at regular intervals through the day. Some of the jobs are failing with the following error recorded in the 'View Job History..':

EXCEPTION: Insufficient memory for this operation. Process Exit Code 2. The step failed.

Will this be cured by increasing the memory available to SQL Server (it has 512Mb already, 1/2 of the total physical RAM)? Also, why are only some jobs failing and others completing? Should I run performance monitor when the next schedule is?

Thanks

Derek