SQL SSIS job failing with out of memory error

Question

SQL SSIS job failing with out of memory error

Saran

Right there with Babe

Points: 760
More actions
October 20, 2023 at 2:05 pm

#4311845
Hi All,
Most of the time SQL SSIS job failing with out of memory error. The package are hosted in SSIS catalog.
My question is - Will SSIS & SSAS use memory within SQL max and min memory (or) out of SQL server?
I have SQL SSIS, SSAS & two instance of DB engine. I am planning to reconfigure memory.
Sample Errors:
Not enough memory resources are available to complete this operation.
The paging file is too small for this operation to complete
A buffer failed while allocating 10485760 bytes.
Insufficient system resources exist to complete the requested service.
The paging file is too small for this operation to complete
Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- This topic was modified 1 year, 1 month ago by Saran.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

SSIS and SSAS memory usage are totally separated from that of SQL Server - and the min/max memory settings on SQL do not affect those.

SSIS does not have a "max memory" - SSAS (specially tabular mode) do have it.

if you have the 3 on same server you will need to spec it (and configure both SQL and SSAS so their memory is limited) so it can take up the 3 loads.

Saran Right there with Babe Points: 760 More actions · Answer 2

Thanks for the update. I have 65GB on server with two SQL instances.

Shall I configure 15 GB to SQL1, 15GB to SQL2, 15 GB to SSAS Tabular & Rest of 20GB to SSIS and OS. Since majority of the tasks are from SSIS.

I have also need to check with my infra team to increase memory.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

only you can know what to allocate to each one based on the load each instance/process needs.

for info SSAS has 3 memory settings you will need to change - there is none on SSIS - here you need to fix the packages and/or number of packages running concurrently.

also note that max memory in SQL is only for part of its memory - SQL will use additional memory on top of that. see https://learn.microsoft.com/en-us/archive/blogs/sqlosteam/memory-manager-configuration-changes-in-sql-server-2012

Saran Right there with Babe Points: 760 More actions · Answer 4

Thanks. I will take look on that link.

Forgot to ask . Does the SSRS use memory from SQL max memory or it's also from outside.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

SSRS also uses outside SQL memory - but for many things (when executing queries and the likes) it fall under SQL memory.

on this I don't have details of how to setup - but some settings are also possible.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 6

max memory only applies to the instance you are configuring it in.

In each SSRS instance, there are memory management configurations you can make in rsReportServer.config. Personally, I have never found it necessary to configure that even in an environment with several thousand users, running more than 10 thousand reports per day.

Your database instances need a max memory setting regardless. Database instances, given the opportunity will eventually take all the memory a system has.

As for what to set them to, only you can answer that question. I would start with capping your database instances first. (you will have to restart their services for it to apply) In SSIS you may get more traction by configuring the buffer counts and buffer sizes that messing with trying to figure out how much memory to leave allocated.