August 13, 2009 at 10:26 pm
Hi Everyone,
Am getting the below Memory related error:701 while i am trying to insert data into a table.
What could be the reason and what should be done in such a scenario's?
To avoid such errors what should i take care before perform bulk insert's?
Please find the attched screen shot which shows the Error Msg.
Thanks in advance.
August 14, 2009 at 6:15 am
Increase the Page File of the system where you are executing these queries and then try again.
you can set the Page File of a system to 1.5 times of the physical available memory.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 7:28 am
Hello Singh,
"set the Page File of a system to 1.5 times of the physical available memory"...
Can you tell how to change this?
-LK
August 14, 2009 at 7:40 am
a quick google search of "how to set page file size windows 2003" (or your OS) will give you many pages on how to.
August 14, 2009 at 7:40 am
Start the System Control panel applet (start - settings - control panel - system)
go to avanced tab and then Click Performance settings
Now again go to advanced tab
Under the Virtual Memory section it will tell the currently configured amount. Click Change.
A list of all partitions and the size of any pagefiles that exist will be listed next to them. To modify the size of an existing pagefile select the drive, e.g. c: and in the "Paging File Size for Selected Drive" enter a new Initial and Maximum size. Click Set when you have changed the values. The minimum size is 2MB but the total size of all pagefiles should be at least the size of memory + 11MB.
If you want to add an additional page file, select a drive that does not currently have a pagefile, e.g. d:, enter an initial and maximum and then click Set.
Once you have completed all changes click OK.
Click OK to the System control panel applet
You will have to reboot the machine for the change to take effect.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 7:51 am
There could be several reasons for the memory error. Automatically increasing the pagefile is not necessarily the best thing to do. It should be at a minimum equal to the amount of memory you have in your server, 1.5 times is better. BUT, the objective should be to have SQL Server optimized to not go into paging, as this then creates a performance issue.
Review this:
Then identify what is using the memory, how much memory is available on the server, how much the OS is using and how much does SQL Server actually have.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 14, 2009 at 7:52 am
Sorry, the link did not show up:
http://msdn.microsoft.com/en-us/library/aa337311(SQL.90).aspx
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply