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.

SQL to Track Running Jobs

Execute below query to find the SQL Jobs running in a server:

Exec msdb..sp_help_job @execution_status = 1

Unable to Restart SQL Agent after restarting SQL Server

When we restart SQLServer, sometimes SQL Agents gets stopped and we may unable to restart the SQLAgent, it remains as Stopped.

In that case execute the below query and try restarting SQL Agent:

EXEC sp_addsrvrolemember 'NT SERVICE\<ServiceName>', 'sysadmin';
GO
You must replace <ServiceName> with SQLServerAgent for the default instance or SQLAgent$InstanceName
for a named instance as below:

EXEC sp_addsrvrolemember 'NT SERVICE\SQLAGENT$BIP1', 'sysadmin';
GO