I am processing 1 million records through datacleansing.
My job failed with below error.
SQL buffer used to batch multiple rows has exceeded the underlying database buffer size
details
9148 12300 DBS-070401 9/13/2013 3:08:23 PM
ODBC data source error message for operation : <[Microsoft][SQL Server Native Client 10.0][SQL
9148 12300 DBS-070401 9/13/2013 3:08:23 PM Server]
There is insufficient system memory in resource pool ‘internal’ to run this query.>.
9148 12300 RUN-051002 9/13/2013 3:08:24 PM
|Data flow df_cleansing|Loader Col_Selection_table_name
9148 12300 RUN-051002 9/13/2013 3:08:24 PM
The load operation for table <table_name> failed because the SQL buffer used to batch multiple rows has exceeded the
9148 12300 RUN-051002 9/13/2013 3:08:24 PM underlying database buffer size. The current buffer size is <24000>. This size can be reset by adding or changing option
9148 12300 RUN-051002 9/13/2013 3:08:24 PM <AL_EngineLoaderSQLBufferSize> in the [AL_Engine] section in the DSConfig.txt file. For example, to set the new buffer size to
9148 12300 RUN-051002 9/13/2013 3:08:24 PM be 30K, you would type AL_EngineLoaderSQLBufferSize=30000.
9148 12300 RUN-051005 9/13/2013 3:08:24 PM |Data flow df_cleansing|Loader Col_Selection_table_name
9148 12300 RUN-051005 9/13/2013 3:08:24 PM Execution of for target <table_name> failed. Possible causes: (1) Error in the SQL syntax;
9148 12300 RUN-051005 9/13/2013 3:08:24 PM (2) Database connection is broken; (3) Database related errors such as transaction log is full, etc.; (4) The user defined in
9148 12300 RUN-051005 9/13/2013 3:08:24 PM the datastore has insufficient privileges to execute the SQL. If the error is for preload or postload operation, or if it is
9148 12300 RUN-051005 9/13/2013 3:08:24 PM for regular load operation and load triggers are defined, check the SQL. Otherwise, for (3) and (4), contact your local DBA.
11780 9296 RUN-050316 9/13/2013 3:08:35 PM |Session Job_provider|Work flow wf_cleansing
Can you please suggest what should i do.
Should i go an dincrease the buffer size in dsconfig or i should do some improvement in database memory or any suggestion would be helpful.
Thanks
Nas7484 (BOB member since 2010-07-28)