SQL Server Timed Out due to Low Memory Space While SSIS Package was Running

SQL Server Timed Out due to Low Memory Space While SSIS Package was Running:

While I was running my data warehouse job (containing many ETL Master Packges) in SQL SERVER 2008 R2, environment I was frequently facing Timed Out issue and SQL server got disconnected, but when I ran single package manually, it ran without failure.

So I performed the below tasks to monitor the performance of the Job:

1. Monitor performance in Task manager: The CPU usage was more than 90% when the job was running.

2. Tracked job even in EVENT VIEWER when the job failed, and got the below error message:

3. Executed below query in SQL Server:

SELECT [name] AS [Name]
      ,[configuration_id] AS [Number]
      ,[minimum] AS [Minimum]
      ,[maximum] AS [Maximum]
      ,[is_dynamic] AS [Dynamic]
      ,[is_advanced] AS [Advanced]
      ,[value] AS [ConfigValue]
      ,[value_in_use] AS [RunValue]
      ,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Max server memory (MB)','Min server memory (MB)')

On executing the above query, I found that the server is configured with maximum config value:

4. Executed the below query to trouble shoot the issue:


EXEC sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'min server memory', '1024'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max server memory', '6000'
RECONFIGURE WITH OVERRIDE

I set Minimum Allowed Memory to 1 GB and Maximum Allowed Memory to 6 GB and now my data warehouse job runs without any issue and consumes less CPU usage.