March 27, 2013 at 10:09 pm
Please help me.
I m try to import data from csv to table using bulk insert but it gives me insufficient memory available in buffer pool error.
what i have to do for resolving this issue.
Regards,
Arjun.
March 28, 2013 at 12:22 am
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFER
run this.. it will help you
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 28, 2013 at 12:25 am
I have ran into this problem when using SSIS to insert delimited columns into a table. Didn#t need to free cache space.
The issue was an incorrect file structure, it was actually FTP'd in binary mode rather than ASCII so had removed all the carriage return/line feeds and as such we went over the limit for a column.
'Only he who wanders finds new paths'
March 28, 2013 at 12:47 am
Thanks you all...
One more thing i want to ask that if i have changed max server memory in sql server property then is it must to restart the service of sql server?.
March 28, 2013 at 12:54 am
No you don't need to restart SQL server if you change the Max Server Memory setting it should automatically adjust to the new level.
Out of curiosity did you increase or decrease the Max server memory?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 28, 2013 at 12:57 am
Jason-299789 (3/28/2013)
No you don't need to restart SQL server if you change the Max Server Memory setting it should automatically adjust to the new level.Out of curiosity did you increase or decrease the Max server memory?
Hey Jason,
can you plz tell me how to change the Max Server Memory?
Never tried this thing...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 28, 2013 at 1:34 am
thanks jasone
March 28, 2013 at 1:58 am
Kapil,
Its a setting on the Instance.
1) Open SSMS and connect to Instance
2) right click on Instance name and select properties
3) Select Memory item
4) Look at the Max Server Memory change here.
Or you can do it in a Script
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096; --Memory in MB
GO
RECONFIGURE;
GO
BEFORE doing this I would suggest you read up on best practices for setting the memory, here are a few URLs that might help.
http://msdn.microsoft.com/en-gb/library/ms178067(v=sql.100).aspx
http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
The first one by Glen Berry is very good.
Usually I allocate about 90% of the available server memory if the server has over 32GB, however In servers with less than 32GB of memory I try and leave at least 2-4 GB for the OS and other tasks to run in.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 28, 2013 at 4:52 am
kapil_kk (3/28/2013)
DBCC FREEPROCCACHEDBCC DROPCLEANBUFFER
run this.. it will help you
Define help. This will remove all data from the buffer and force a recompile of all queries. If the data load was dependent on any data being in the buffer, it now has to be reloaded.
I'd be very wary of following this approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2013 at 7:40 am
Are you on a 32 bit sql server install by any chance?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply