0

I have a ETL job running, which is scheduled for every 5 minutes from 1 AM to 9 PM everyday. Generally it takes 10 minutes to execute that package, But unfortunately the first cycle i.e. at 1AM is taking 2 hour or 3 hours or 4 hours from last few days . When I see the Reports from integration service catalogue I see following message:

Data Flow Task:Information: The buffer manager failed a memory allocation call for 65520 bytes, but was unable to swap out any buffers to relieve memory pressure. 74 buffers were considered and 72 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked

and then:

Data Flow Task: The buffer manager has allocated 65520 bytes,even though the memory pressure has been detected and repeated attempts to swap buffers have failed

joeprince
  • 1
  • 1
  • I would suggest running SQL Profiler to see what else is happening when you get these slow downs. Also, any backups running at the same time. Any other jobs likely to conflict with your tables? – BIDeveloper Sep 22 '16 at 14:32
  • I thought of it, but its a production server and in dev the ETL Job is running fine...and no other job is conflicting with it @BIDeveloper – joeprince Sep 22 '16 at 14:50

1 Answers1

0

Can you stop your SSIS package or SQL job for some time and do a Pro cache flush on your DB server; Once completed can you please try to rerun the package ?

Otherwise try to get touch with DBA team and perform some cleanup in TempDB , try to find out enough space is available in TempDB;If not try doing shrink the space within TempDB and try to run the SSIS package manually ?

If it all fails, Please check you Prod DB server for any memory pressure / RAM usage / CPU processing time ?

SubbuCGI
  • 1