Tuesday, July 8, 2014

Execution of SQL Agent Job using Seed File

Purpose of the Article

This article is devised to give a way of running the jobs on demand irrespective of scheduled process. This is very useful in such a case when there is no fixed time for data processing. Incoming data can come anytime and at the same time, system should run the job for data processing. To achieve this, many times we schedule our jobs to run at every half an hour/ 10 mins/ 15 mins etc which in turn wastage of system resources. We can control unnecessary execution of job by using Seed file that tells to Job whether it has to start execution or not.
What is Seed File?
Seed file can be any type of blank file (.txt,csv,.xls) that can be used as input parameter to start the execution of any process. For quintessence-

IF Seed file is available
BEGIN
            START THE PROCESS
END
ELSE IF Seed file is not available
BEGIN
BYPASS THE PROCESS
END

How to control the execution of jobs using Seed file?
1.      To control the execution of jobs, we need an additional step that should be the first step of the job which should check whether seed file is available in the source folder or not.
2.      If seed file is available then job should go to next step and execute as expected and at last step, Job should delete the seed file.
3.      If seed file is not available in the source folder then Job should by pass the steps and should quit with reporting success.
Steps to control the execution of Jobs using seed file:
1.  Create first step of the job for checking whether file is exists or not. For checking file existence and Deletion, we are using XP_FILEEXIST and XP_CMDSHELL extended stored procedures. Below is the code which checks whether file is exists or not.

If file exists, we are simply doing select 1
If file does not exists then we are raising the error with message- ‘Seed file is not available’

DECLARE @Flag INT
DECLARE @FilePath VARCHAR(500)
DECLARE @DelFilePath NVARCHAR(500)
SELECT @FilePath = '\\IND-SQL-01\Processing\Start_Processing.txt'


EXEC MASTER..XP_FILEEXIST @FilePath, @Flag OUT
IF @Flag = 1
BEGIN
       SELECT 1
END
ELSE
BEGIN
   RAISERROR('Seed File is not available',16,1)

END
GO

Fig showing the first step of the job for checking file existence

2.      Create one more step that should be the last step of the job that should delete the seed file if exists to clean the source folder for next execution. Below is the code for deleting the file:




DECLARE @Flag INT
DECLARE @FilePath VARCHAR(500)
DECLARE @DelFilePath NVARCHAR(500)
SELECT @FilePath = '\\IND-SQL-01\Processing\Start_Processing.txt'
SELECT @DelFilePath = 'DEL' + SPACE(1) + @FilePath


EXEC MASTER..XP_FILEEXIST @FilePath, @Flag OUT
IF @Flag = 1
BEGIN
   EXEC MASTER..XP_CMDSHELL @DelFilePath

END


                 Fig showing the last step of the Job for cleaning the seed file

3.      Go to Step first then Edit then Advance. Set the below setting
a.      On Success : Go to Next Step
b.      On Failure:  Go to Last Step.

4.      Job Steps:

In this way, we can bypass the processing steps if seed file is not available and we can save unnecessary utilization of system resources required for execution of the job.


Conclusion
By using the above mentioned steps, we can implement on demand job execution functionality.


----------------------------   Author -  Vishal Jharwade  ----------------------------------

Friday, February 21, 2014

How to implement auto disable functionality of SQL Agent Jobs

Purpose of the Article

This article is devised to give a way of auto disabling the SQL Server Agent jobs in case of failures. In some cases, it is required to disable the job if failed otherwise SQL Server Agent will keep trigger the job and job will keep processing the data, it may lead to huge data discrepancy and other issues like unnecessary failures notifications to the users etc.

How we can implement auto disable functionality of jobs in case of failures
1.      Get list of all the jobs having status as failed using msdb.sysjobs and msdb.jobhistory tables.
2.      Update the Enabled flag of the failed jobs to 0 in msdb.sysjobs table
3.      There should a job  e.g ‘Auto_Disable_Failed_Jobs’ which should run the T-SQL script for above 2 steps. Frequency of the job can be every 1 or 2 mins.        
Steps to auto disable the Jobs in case of failures
1.      Below is the script which gives the list of failed jobs as well as update statement to disable the failed jobs:

USE MSDB
GO

--Declare Table Variable
DECLARE @FailedJobs TABLE
(NAME VARCHAR(500),
[MESSAGE] VARCHAR(2000),
LASTRUNDATE VARCHAR(500),
LASTRUNTIME VARCHAR(500))

-- Getting Failed Jobs
INSERT INTO @FailedJobs
(NAME,[MESSAGE],LASTRUNDATE,LASTRUNTIME)


SELECT 
    J.[NAME] AS [NAME],  
    H.MESSAGE AS [MESSAGE],  
    H.RUN_DATE AS LASTRUNDATE,   
    H.RUN_TIME AS LASTRUNTIME
FROM SYSJOBHISTORY H  
    INNER JOIN SYSJOBS J
              ON H.JOB_ID = J.JOB_ID  
  WHERE J.ENABLED = 1   
        AND H.INSTANCE_ID IN 
        (SELECT MAX(H.INSTANCE_ID)  
            FROM SYSJOBHISTORY H GROUP BY (H.JOB_ID))  
        AND H.RUN_STATUS = 0  
                            
 
  --Disabling the Failed Jobs
  UPDATE SJ
  SET [enabled] = 0
  FROM sysjobs SJ
       INNER JOIN @FailedJobs F
              ON SJ.NAME = F.NAME


             
2.      Create a SQL Agent Job e.g Auto_Disable_Failed_Jobs and run the above code in the job step:

3.      If we don’t want to check the failure status for all the jobs available in an instance of SQL Server then we can filter the job name within the script.
4.      Once Job issue is resolved, System admin or DB admin / support team can enable the job manually.


Conclusion
By using the above mentioned steps, we can implement the auto disable functionality of failed jobs.


----------------------------   Author- Vishal Jharwade----------------------------------