T-SQL to Disable all Jobs in in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job

SSIS Package Runs Fine in Integration Server but Fails From SQL Agent Job:

This is the most common issue faced when we deploy packages in 64 bit system. When we create any package with Run64bit set as false, this issue occurs.

In order to overcome the 32\64 bit environment issue, it better we use command line in our job to execute the packages.

Use the below command:

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec /DTS "\MSDB\ETLFolder\ETLExtractMaster" /SERVER DW01P /CHECKPOINTING OFF  /REPORTING V

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

Batch File Script to deploy SSIS packages via DTUTIL command

The below code helps to deploy ssis packages to o MSDB folder in a server:

@Echo Off

Echo.
Echo.
Echo SSIS Package Installation Script
Echo.

if %1a == a goto Error
if %2a == a goto Error
if %3a == a goto Error

Echo.
Echo.
Echo Deployment Server: %1
Echo -----------------------------------------------------
Echo --This will delete any %3 data mart files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause
REM Goto Out


REM Remove Existing files and directory on Server
for %%f in (%2"\*.dtsx") do (
Echo Now Removing: %%~nf
dtutil /Q /SourceS %1 /SQL "\%3\\%%~nf" /Del
)

dtutil /Q /SourceS %1 /FDe "SQL;\;%3"

:Create

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;%3"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause

:Out
REM copy the SSIS Packages to the server
for %%f in (%2"\*.dtsx") do (
Echo Now Copying: %%~nf
dtutil /Q /DestS %1 /Fi "%%f" /C "SQL;\%3\\%%~nf"
)


Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy SSIS Packages [servername] [Source File Path] [MSDB Deploy Folder]
Echo.
Echo.

Pause

:End

1. Copy the above code and crete a bat file (e.g., DeploySSIS).
2. Open Command Prompt and navigate to th ebatch file folder
3. execute the command DeploySsis.bat [SERVERNAME] [FILEPATH] [MSDB Sub-Folder]

SQL Function to Convert Seconds to HH:MM

Create a function as like below:

CREATE FUNCTION [dbo].[ufn_GetHHMM] ( @pInputSecs   BIGINT )
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @HHMM       Varchar(Max)

IF @pInputSecs < 60 and @pInputSecs<>0
BEGIN
SET @HHMM= '0:01'
END
ELSE
BEGIN
SET @HHMM=ISNULL(CAST(@pInputSecs/3600 AS VARCHAR(MAX)) +':'
+RIGHT('0'+CAST(ROUND(CAST((@pInputSecs%3600.0)/60.0 as float),0) as varchar(MAX)),2),'0:00')
END

RETURN @HHMM


END

--To Get Results

SELECT dbo.ufn_GetHHMM(DurationinSeconds) --Assume you have column name 'DurationinSeconds'