Hello, we are using SQL Server 2008 R2 in Windows Server 2008. 4 processors and 8192 MB RAM. There are 12 subsystems in the subsystems cache. We are doing everyday periodic ETL. And everything is fine. But on all data reload which occurs on every Saturday is a problem. Two weeks ago everything loaded without problem. But last Saturday i had an error. In error log is written "There is insufficient system memory in resource pool 'internal' to run this query." I wonder if server restart before full data reload would help? Because 2 weeks this server wasn't restarted. As you can see server's hardware isn't big..

What does the below return? --sql server uses the memory select CONVERT(VARCHAR,CAST(bpool_committed *8 AS MONEY),1)AS [SIZE], bpool_committed,bpool_commit_target from sys.dm_os_sys_info Do you assign MAX memory param to sql server? What does ETL reload, can you show an example?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

This select returned : SIZE bpool_committed bpool_commit_target 7,573,656.00 946707 958561. No, i didn't assign memory param to sql server? Is it useful? ETL does this things: Extracting, Transforming and Loading dimensions. And then i have 2 fact(measure) tables ~ 20 millions rows. Dimension succeeded fine. JOB stuck on second measure step (first measure ETL done fine). Then i re-runned this step. Step succeded fine. Then i have step with creating indexes on all column. On half columns indexes was created and then i got error "The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped". And again i re-runned "Index creating" step. Then step succeded fine..

>>No, i didn't assign memory param to sql server? Is it useful? Yes , it could be. Make sure that account you run SQL Server has to be a member of Local Group policy named Locked Page in memory sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'max server memory', 6144 --6GB to sql server and leave 2gb to OS RECONFIGURE On what step of the job you get "There is insufficient system memory in resource pool 'internal' to run this query." If you exclude CREATE INDEX step you still get that error?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Every fact contain "mini steps" - DTSX tasks. Extract, Transform, Load. Create Index on all columns. First measure succeded fine. Then second Measure stuck on: (First time) Error: "There is insufficient system memory in resource pool 'internal' to run this query" on Transform Task (Update columns) (Second time) Error: "The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped" on index creating task.. Created index on half columns and then returned an error..

You are providing piece-to-piece info :-))) So what does EXTRACT step do????Any example?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

I'm sorry :) Extract - load data from production (Data Flow). Only load data. Transform - update loaded table. Alter new columns. Load - load transformed (staging) table to DataWarehouse table. In periodic load delete old records (using last modify date) in full load - all staging data load to Data Warehouse table. Check if there was full load. If yes than create indexes on all columns.

>>Extract - load data from production (Data Flow). Only >>load data. Ok, do you have OLEDB provider feature named fast load? How big is your load? If it is , is that possible to divide it into a small chunks? Make a changes I suggest you above and see if it still happensBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Yes, extract have an option "fast load". Extract is about 20 millions rows. I would like question. Server restart would help? I mean restart before ETL process.

Yes it would, but I think it matter ofn time you will face the problem againBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/