Insufficient Memory 1

[manmaria]

I am getting the following error in one of the nightly job

"There is insufficient system memory to run this query. [SQLSTATE 42000] (Error 701). The step failed "

Some times this job is successful and sometimes failed with this error.
Our configuration.
SQL05, Simple Recovery,Auto Shrink True,Log file initial Size 300MB,Autogrowth by 10 %, Unrestricted growth.
Backup for the database is been taken every night without any problem. Available space on this database 400mb.

TIA

 

[ptheriault]

How much memory is in the server and how large is your page file?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[manmaria]

Physical Memory is 3GB. How do I find the size of a page file?

TIA

 

[ptheriault]

click start->run->type msinfo32 <enter>

In the summary for system resources at the bottom you will fund memory and page file info.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[manmaria]

Thank you Paul.
Page file space is 3GB and the total physical memory is 3GB.

 

[ptheriault]

How much memory is your SQL instance configured to use? You may need to allocate more to your SQL instance.
It sounds like you didn't have enough memory allocated and your process failed because of it. A general rule with page file is it can be 2 or 3 times larger than you physical memory. In your case if the disk is big enough you could have a 12 gb page file. This will allow processes to page more to disk allowing other processes to run. Paging is not usually a good thing but in this case I would rather have higher paging instead of failing jobs because of insufficient memory.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[manmaria]

Thanks again. I think we are using the max available memory(around 3GB)for the SQL Instance. Available disk space is around 60GB.
How do I change the page file?
TIA

 

[ptheriault]

>>How do I change the page file?

You should have your server admins do it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[manmaria]

Thanks. I think I found how to change. I have not tested yet but will do soon.
1.
Open Computer Management (Local)

2.
In the console tree, right-click Computer Management (Local), and then select Properties.

3.
On the Advanced tab, click Performance Options, and under Virtual memory, click Change.

4.
In the Drive list, click the drive that contains the paging file you want to change.

5.
Under Paging file size for selected drive, type a new paging file size in megabytes in the Initial Size (MB) or Maximum Size (MB) box, and then click Set.

Thanks for your help.

 

[ptheriault]

No problem,
There is no guarantee that this will correct you problem. But short of adding more memory this should help.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

You should also look at your query and why it is taking so much memory. Chances are it is not properly performance tuned.

"NOTHING is more important in a database than integrity." ESquared