Click here to Skip to main content
16,019,285 members
Articles / Database Development / SQL Server / SQL Server 2008

How to execute multiple long running SQL Statements Asynchronously in small chunks

Rate me:
Please Sign up or sign in to vote.
4.90/5 (25 votes)
28 Mar 2013CPOL13 min read 208.5K   1.3K   73   36
How to execute multiple long running SQL Statement Asynchronously in smaller chunks

Introduction

Completing task  Asynchronously in case of long running query processing is very helpful in some scenario. It ensures maximum use of hardware resources as well. In case of non set based modern high level programming language such as C# or Java has versatile facilities, libraries and patterns  for asynchronous programming. But what about SET based language such as SQL? There is no straight forward way to execute SQL statements  in parallel mode. In SQL Server there some way to do such stuff like using SQL Server Service Broker or through CLR stored procedure. Service Broker actually is a process of sending and receiving messages which can be sent to same or any remote database of another SQL Server instance. Whereas CLR needs different set of programming expertise, it also has some deployment issue. Today I am going to show you the same implementation using SQL Server Agent Job. In this article you also came to know, how huge number of long running SQL statements will be executed in some smaller configurable chunks. 

Background 

In my recent data ware house project, client has a requirement to validate a table data against some wide range of predefined rules. The table has more than 150 columns. Each column's data need to be validated with these rules. Let us be familiar with some of them.

SL#

Column

Rule

1

Customer DOB

DOB Must be a valid date

DOB must be less than the purchase date. 

The date format should be MM-DD-YYYY

2

Credit Card Number

Card Number should not be an Empty value

Should be a valid Card Number

Should be a valid Card Type

Expiry Date must be within a range of 1997 To 2020 

Table 1: Rule against column. 

In addition, another requirement is to keep the garbage data into another table for reporting purpose so that client can view the garbage data and fix them. To accomplish the job we have dedicated to created stored procedures for each rule. As a result we had to created about 35 distinct stored procedures to validate the customer table data. Client usually sends us customer data in text files and we bulk load them into staging tables. But our main challenge was to validate the data of all 150 columns by running on average 4 rules (4 stored procedures) against each columns. Our customer table contains more than 3 million of records and each validation rule stored procedure took considerable time to select the garbage value and save them to a Error Log Table. In this situation running these stored procedures in parallel mode and 10-12 procedures at a time (a chunk)  was an splendid solution for us to get the job done. Alright, no more talk, let us jump to the actual implementation. 

Metadata Management

As I spoke earlier that we will use SQL Server Agent Job to run our stored procedure Asynchronously .So in run time you will have very minimal control over the running stored procedures or statements. In this situation we have used a metadata table that stores the stored procedures execution status like when they started and finished or any error message during execution period. The table creation scripts would be: 

SQL
CREATE TABLE AsyncProcessStatusLog
(
	Id                      INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	StoredProcedureName     VARCHAR(100) NOT NULL,
	StartTime               DATETIME NOT NULL,
	EndTime                 DATETIME NULL,
	ProcessStatus           CHAR(1) NOT NULL
	CHECK  (([ProcessStatus]='F' OR [ProcessStatus]='S' OR [ProcessStatus]='R')),
	 /*------- R=Running S=Success F=Fail */
	ErrorMessage			VARCHAR(2000) NULL 
)

Process Status: 

  •         "R" indicates the procedures is running state. 
  •          "F" indicates the procedure failed and an exception occurred. 
  •          "S" means the procedures successfully executed. 

After that we need to create a stored procedure named "InsertUpdate_AsyncProcessStatusLog_usp " that will insert/update status into the "AsyncProcessStatusLog " table. 

SQL
CREATE PROCEDURE InsertUpdate_AsyncProcessStatusLog_usp
	@StoredProcedureName VARCHAR(100),
	@ProcessStatus CHAR(1),
	@ErrorMessage VARCHAR(2000)
AS
BEGIN
	
	DECLARE @now DATETIME = GETDATE()
		
	IF NOT EXISTS (
	       SELECT 1
	       FROM   AsyncProcessStatusLog
	       WHERE  StoredProcedureName = @StoredProcedureName
	   )
	    INSERT INTO AsyncProcessStatusLog
	      (
	        StoredProcedureName,
	        StartTime,
	        EndTime,
	        ProcessStatus
	      )
	    VALUES
	      (
	        @StoredProcedureName,
	        @now,	--Procedure start executing
	        NULL,
	        'R'		--We know this status is "Running" at this stage.
	      )
	ELSE
	    UPDATE AsyncProcessStatusLog
	    SET    EndTime              = @now,	--Execution fnishing time.
	           ProcessStatus        = @ProcessStatus,--Process Status (F or S will come here)
	           ErrorMessage         = @ErrorMessage
	    WHERE  StoredProcedureName  = @StoredProcedureName
END

How to write The Stored Procedures/Statements for parallel execution

Now let me prepare some sample stored procedures that will be run in parallel. Some convention need to be followed when creating them.  Have a look below:

SQL
CREATE PROCEDURE CheckValidDate_usp
--Parameter goes here
AS
BEGIN
 BEGIN TRY
	--Saving this SP status to metadata table that it is in Running state.
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'R',
		    @ErrorMessage = NULL
		
	------------------------------------------
	--Details Implementation (Business Logic)
	--Details Implementation (Business Logic)
	--Details Implementation (Business Logic)
	WAITFOR DELAY '00:00:10'
	-------------------------------------------
	--Saving this SP status to metadata table that it has successfully finished the task.
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'S',
		    @ErrorMessage = NULL
 END TRY
 BEGIN CATCH
	--Oh!! some error occured and keeping this information here.
	DECLARE @ErrorMsg VARCHAR(2000) = ERROR_MESSAGE()
	EXEC InsertUpdate_AsyncProcessStatusLog_usp @StoredProcedureName = 
		    'CheckValidDate_usp',
		    @ProcessStatus = 'F',
		    @ErrorMessage = @ErrorMsg
 END CATCH
END

Maintaining Stored Procedures execution status by executing "InsertUpdate_AsyncProcessStatusLog_usp" stored procedure. 

  • Setting Initial Running Status (@ProcessStatus ='R') by the top most statement.

  • Updating with Success Status (@ProcessStatus ='S') by the statement placed just above the END TRY block. 

  • Updating with Failed Status (@ProcessStatus = 'F') by the statement inside CATCH block with additional parameter @ErrorMessage.                    

  • ·          Business Logic Block: 

    This block usually contains main TSQL code to satisfy the business logic. I have used WAITFOR DELAY '00:00:10' here  to have an impression that the stored procedure takes 10 seconds to finish for demo purpose. (In real life situation this will be replaced by actual business TSQL logic.)

    Sample Stored Procedures: 

     I have written 7 stored procedures against each rule by following the convention mentioned above. Please have a look on the attached TestStoredProcedures.sql file (After extracting AsyncSQLScripts.zip).  

    SL#

    Rule

    Associated Stored Procedure

    1

    DOB Must be a valid date

    CheckValidDate_usp

    2

    DOB must be greater than the purchase date.

    CheckDateGreaterThanAnotherDate_usp

    3

    The date format should be MM/DD/YYYY

    CheckValidDateFormat_usp

    4

    Card Number should be an Empty value

    CheckIsRequired_usp

    5

    Should be a valid Card Number

    CheckValidCreditCardNumber_usp

    6

    Should be a valid Card Type

    CheckCardTypeValid_usp

    7

    Expiry Date must be within a range of 1997 To 2020

    CheckIsDateInValidRange_usp

    Table 2: Rule wise stored procedure. 

    The main attraction!! The script that executes Stored Procedures Asynchronously via Agent Job: 

    We have our  Stored Procedures and Metadata table ready. Now let us have a look on the main attraction, the scripts which will create SQL Agent Job steps on the fly and run our Stored Procedures In parallel mode. 

    SQL
    CREATE PROCEDURE ExecuteSQL_ByAgentJob_usp(
        @SqlStatemet            VARCHAR(4000),
        @SPNameOrStmntTitle     VARCHAR(100),
        @JobRunningUser         VARCHAR(100) = NULL,
        @JobIdOut               UNIQUEIDENTIFIER OUTPUT
    )
    AS
    BEGIN
    	
    	SET NOCOUNT ON;  
    	
    	DECLARE @JobId          UNIQUEIDENTIFIER,
    			@JobName        VARCHAR(250) = NULL,
    			@DBName         VARCHAR(100) = DB_NAME(),
    			@ServerName     VARCHAR(100) = @@SERVERNAME
    	
    	
    	--Creating Unique Job Name by combining @SPNameOrStmntTitle and a GUID.		
    	SET @JobName = @SPNameOrStmntTitle + '_' + CONVERT(VARCHAR(64), NEWID()) 
    	
    	--Currently logged user name will be used to execute the job if not provided one.
    	IF @JobRunningUser IS NULL
    		SET @JobRunningUser = SUSER_NAME()
    	
    	--Adds a new job executed by the SQLServerAgent service
    	EXECUTE msdb..sp_add_job @job_name = @JobName, @owner_login_name = @JobRunningUser, 
    	@job_id = @JobId OUTPUT 
    	
    	--Targets the specified job at the specified server 
    	EXECUTE msdb..sp_add_jobserver @job_id = @JobId, @server_name = @ServerName 
    	
    	--Tell job for its about its first step.
    	EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step1', @command 
    	= @SqlStatemet,@database_name = @DBName, @on_success_action = 3 
    	
    	--Preparing the command to delete the job immediately after executing the statements 
    	DECLARE @sql VARCHAR(250) = 'execute msdb..sp_delete_job @job_name=''' + @JobName + ''''
    	
    	EXECUTE msdb..sp_add_jobstep @job_id = @JobId, @step_name = 'Step2', @command = @sql 
    	
    	--Run the job
    	EXECUTE msdb..sp_start_job @job_id = @JobId
    	
    	--Return the Job via output param.
    	SET @JobIdOut = @JobId
    END

    Main logic taken from here  

    The stored procedure works as follows:   

    •         Creates a job with an unique name, register it to the sever. The has two steps.   
    •          In Step-1:  the job executes the provided SQL Statement. 
    •          In Step-2 : the job executes command to delete itself. 
    •     The procedures also return the JobId for further reference. 

    Breaking the queries into small chunks

    Now we have everything to run the our stored procedures asynchronously. But what about to run them in a small chunk. Now let me say few words about the benefits of executing resource intensive talks in a smaller chunk. 

    • if we run 100 parallel tasks there is a significant chance for the server to go out of resource  with a error like " Insufficient System Memory To Run This Query".
    • If we need to forcefully stop query execution only current chunk will be stopped and it ensures no further processing.  

    We have total 7 stored procedures here, we will run 3 procedures at a time. In that case the chunk execution scenario will be as follows:    

    SL# 

    Stored Procedure 

    Chunk #

    CheckValidDate_usp 

    1

    CheckDateGreaterThanAnotherDate_usp

    CheckValidDateFormat_usp

    CheckIsRequired_usp

    2

    CheckValidCreditCardNumber_usp

    CheckCardTypeValid_usp

    CheckIsDateInValidRange_usp

    3

    Table 3: Chunk wise script distribution 

    This chunk size actually depends on case to case so I prefer in real life, better  to keep this value will be in a configuration table so that any time this value can be changed.

     Arrangements for running the SPs asynchronously: 

    To the run the queries asynchronously we need to keep in mind the following arrangements.  

    • Prepare and keep all queries/stored procedures(with required parameters) in a temp table or table variable.
    • Execute the first chunk of using  "ExecuteSQL_ByAgentJob_usp" SP with the dynamic statement prepared at previous step and wait until first chunk to be finished and run the subsequent chunks.
    • Monitor the scripts by querying the metadata table "AsyncProcessStatusLog". 
    • Notify user for any untoward situation during run time.  

    Now let us discuss each steps in details.   

    SQL
    CREATE PROCEDURE Customer_Validator_usp
    --Any parameter goes here
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	---Cleanup previous metadata
    	---Just for testing purpose. Think about your won implementation style.	
    	DELETE FROM   AsyncProcessStatusLog
    	
    -----------------PART1------------------------------------
    	DECLARE @Scripts TABLE (
    	            Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    	            Script NVARCHAR(4000)
    	        )
    	
    	INSERT INTO @Scripts
    	SELECT 'EXEC CheckValidDate_usp'--Additional parameters 
    	UNION ALL
    	SELECT 'EXEC CheckDateGreaterThanAnotherDate_usp'
    	UNION ALL
    	SELECT 'EXEC CheckValidDateFormat_usp'
    	UNION ALL
    	SELECT 'EXEC CheckIsRequired_usp'
    	UNION ALL
    	SELECT 'EXEC CheckValidCreditCardNumber_usp'
    	UNION ALL
    	SELECT 'EXEC CheckCardTypeValid_usp'
    	UNION ALL
    	SELECT 'EXEC CheckIsDateInValidRange_usp'
     
    -----------------PART2------------------------------------	
    	
    	DECLARE @sql NVARCHAR(4000),
    	        @Itr INT,
    	        @RecCount INT,
    	        @ScriptTitle VARCHAR(200),
    	        @JobId UNIQUEIDENTIFIER
    	
    	DECLARE @TotalScriptSentToJob     INT = 0,
    			--Hard code here.Should be configurable.
    	        @MaxValidatonProcedureToRunAtaTime INT = 3,
    	        @IsChunkProcessing        BIT
    	
    	SET @Itr = 1 --Seeting the initial value.
    	SET @RecCount = (
    	        SELECT COUNT(*)
    	        FROM   @Scripts
    	    )
    	
    -----------------PART3------------------------------------
    	WHILE (@Itr <= @RecCount)
    	BEGIN
    	    SELECT @sql = t.Script
    	    FROM   @Scripts t
    	    WHERE  id = @Itr
    	    --Just o identify the script name getting first 10 char of the SP
    	    SET @ScriptTitle = LEFT(REPLACE(@sql, 'EXEC ', ''), 10) 
    	    
    	    EXEC ExecuteSQL_ByAgentJob_usp
    	         @SqlStatemet = @sql,
    	         @SPNameOrStmntTitle = @ScriptTitle,
    	         @JobRunningUser = 'sa',
    	         @JobIdOut = @JobId OUTPUT
    	  
    -----------------PART4------------------------------------
    	    SET @TotalScriptSentToJob = @TotalScriptSentToJob + 1
    	    
    	    --Wait for some seconds until send the next procedure to job.
    	    --It may take some time to initialize the job and write to metadata table.
    	    IF (@TotalScriptSentToJob = @MaxValidatonProcedureToRunAtaTime)
    	    BEGIN
    	        SET @TotalScriptSentToJob = 0
    	        SET @IsChunkProcessing = 1
    	    END
    	    
    -----------------PART5------------------------------------	    
    	    IF (@IsChunkProcessing = 1)
    	    BEGIN
    	        DECLARE @Result INT
    	        
    	        EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp
    	        PRINT 'I am waiting the chunk to be finished.'
    	        IF (@Result = -1)
    	        BEGIN
    	            RAISERROR ('Exception occured in some srored procedure.', 17, 2) 
    	            RETURN -1
    	        END
    	        ELSE
    	        BEGIN
    	            SET @IsChunkProcessing = 0
    	        END
    	    END
    	    
    	    ---Cleanup section------	
    	    SET @sql = ''	    
    	    SET @Itr = @Itr + 1
    	END
    	
    -----------------PART6------------------------------------
    	--Wait for the last chunk to be finished.
    	
    	EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp
    	PRINT 'I am waiting the last chunk to be finished.'
    	
    	IF (@Result = -1)
    	BEGIN
    	    RAISERROR ('Exception occured in some srored procedure.', 17, 2) 
    	    RETURN -1
    	END
    	
    	--Allow other scripts to run.
    	PRINT 'I am finished!'
    END

    PART-1 Preparing and Storing Scripts in a table variable

    The scripts are kept into a table variable in a straight forward way just to reduce complexity. But in real life situation please find out your won suitable way to prepare the scripts with associated parameters dynamically and store them to the table variable. 

    PART-2 Variable declaration Section

    Here required variables have been declared. Not all are needed to be described here but I would like to tell few words about "@TotalScriptSentToJob" and "@MaxValidatonProcedureToRunAtaTime" variables. The "@MaxValidatonProcedureToRunAtaTime" variable stores the value of how many Stored Procedures will run at a time(Chunk Size) and "@TotalScriptSentToJob" keeps the incremented value of how many Stored Procedures already sent to SQL Agent for asynchronous processing. The purpose of "@IsChunkProcessing" is to decide whether the system will go to wait state to stop processing any other queries until the current chunk to be finished. 

    PART-3 Beginning of the WHILE  LOOP

    Prepare Job name, and sending the command to the SQL Agent job for parallel processing. 

    PART-4 Decides to go for WAIT state

    After sending scripts to the Agent Job the value of "@TotalScriptSentToJob" get incremented and get compared with "@MaxValidatonProcedureToRunAtaTime" to determine whether max number of Stored Procedures already been sent  against  a chunk or not. If max number of Stored Procedures already sent then it reinitialize "@TotalScriptSentToJob" with "0" and "@IsChunkProcessing " with "1" . 

    PART-5 Wait State:  

    If get "@IsChunkProcessing =1". The system goes to wait state until the current asynchronously running stored procedures to be finished. How system goes to WAIT state after finishing a chunk, has been described in details later in this article. This PART also terminate the whole process for any error occurs during run time. 

    PART-6 WAIT State When Last chunk value is always less than the actual chunk

    At the end, once again the system check for any running/active statements and waits for them to be completed. This situation takes place when the last chunk value is always less than the actual chunk  value. 

    To get a clear idea about this, just have a look on the Table 3 (Chunk wise script distribution) where we have total 7 stored procedures and we have divided them into 3 chunks like 3+3+1. In the last chunk only one procedures will run. In this situation the following code block never comply with this situation.   

    IF (@TotalScriptSentToJob >= @MaxValidatonProcedureToRunAtaTime) 

    Because the value of  "@TotalScriptSentToJob" will be "1" but our initial chunk size is set to 3 (@MaxValidatonProcedureToRunAtaTime=3) . So the value of "@IsChunkProcessing" will never a get a value "1". Thus no WAIT state will happen for this chunk inside the WHILE LOOP. To get rid from this , the "EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp" statement  has been used once again after END block of WHILE loop.

    How system maintains WAIT state after finishing a chunk

    The system goes in wait state by executing "EXEC @Result = Wait_Unitl_Chunk_ToBe_Finished_usp". This procedures run a infinite loop and checking the status of each procedures running under this chunk by querying the "AsyncProcessStatusLog " metadata table.  

    Let us see the implementation: 

    SQL
    CREATE PROCEDURE Wait_Unitl_Chunk_ToBe_Finished_usp
    AS
    BEGIN
    	WHILE (1 = 1)
    	BEGIN
    	    WAITFOR DELAY '00:00:3'
    	    
    	    IF EXISTS(
    	           SELECT 1
    	           FROM   AsyncProcessStatusLog aps
    	           WHERE  aps.ProcessStatus = 'F'
    	       )
    	        RETURN -1
    	    
    	    
    	    IF NOT EXISTS(
    	           SELECT 1
    	           FROM   AsyncProcessStatusLog aps
    	           WHERE  aps.ProcessStatus = 'R'
    	       )
    	        RETURN 1
    	END
    END

     You may remember from "How to write The Stored Procedures/Statements" section  that when the each procedures start running it writes a row in the metadata table with an initial status (ProcessStatus) "R" indicates "Running". For any exception the status is updated with a value of "F" indicates "Failed". If the procedures runs successfully the status becomes "S"  means "Success".  The infinite loop breaks when there is no active (running) queries in the job.  The procedure return 1 in normal situation and -1 if any exception occurs.  

    Using the code 

    So far we have enough idea about the architecture. Now let's run the scripts and see the how queries are running in parallel and chunk by chunk.

    1. Please make sure SQL Agent Service is running. 
    2. Extract AsyncSQLScripts.zip  and execute DB_Metadata_Table.sql. This will create a test database with metadata table. Afterwards, execute TestStoredProcedures.sql, ExecuteSQL_ByAgentJob_usp.sql , Wait_Unitl_Chunk_ToBe_Finished_usp.sql  sequentially. 
    3. Finally execute Customer_Validator_usp.sql. Now we are ready to test our code. Close all open window of SSMS. 
    4. Open two new blank query tab. Write following script in the respective tabs. 

    Query Tab 1

    SQL
    USE TestAyncDB
    GO
     
    EXEC Customer_Validator_usp

    Query Tab 2

    SQL
     SELECT apsl.StoredProcedureName,
           apsl.StartTime,
           apsl.EndTime,
           DATEDIFF(second, apsl.StartTime, apsl.EndTime) AS ElapsedSeconds
    FROM   AsyncProcessStatusLog apsl
    ORDER BY
           apsl.StartTime
    1. Go to Window Menu and Press "New Vertical Tab Group"  just to see the windows  side by side.
    2. Now execute  the scripts of Query Tab 1 ( "EXEC Customer_Validator_usp") afterwards, quickly switch to Query Tab 2 and press F5 continuously after couple of seconds. 
    3. You will see 3 stored procedures (First Chunk) start running  at the same time(In Parallel) like below: 

    Image 1  

    Figure 1: Query Running in parallel mode for first chunk.

    in the image below, the second chunk has stared and one stored procedure already finished and other two procedures is running in parallel. 

    Image 2

    Figure 2: Second chunk is running.

    The next image shows all Stored procedures finished running asynchronously in 3 separate chunks.

    Image 3

    Figure 3: All stored procedures has finished running in 3 chunks.  
    1. Now go back to Query Tab 1 and see the "Messages"  tab in the result pane. You will find something similar like this. 

     Image 4

    Figure 4: Message generated by SSMS.  

         System goes to wait state after executing each chunk (3+3+1). 

    The Real Benefit

    If we would run the query synchronously(one after another) the first stored procedure will wait for the second procedures to be finished. In that case, it will take 1 minute 10 seconds to finish (as we have put several  WAITFOR DELAY inside the stored procedures).  

    Stores Procedure

    WAITFOR DELAY (Seconds)

    CheckValidDate_usp

    10

    CheckDateGreaterThanAnotherDate_usp

    10

    CheckValidDateFormat_usp

    15

    CheckIsRequired_usp

    5

    CheckValidCreditCardNumber_usp

    10

    CheckCardTypeValid_usp

    10

    CheckIsDateInValidRange_usp

    10

    Total

    70 Seconds

     Table 4: WAITFOR DELAY setting inside each stored procedures. 

    Now let us run another query to determine how much time it took to complete all 7 stored procedures in asynchronous mode. 

    SQL
    SELECT MIN(apsl.StartTime)     MinStartTime,
           MAX(apsl.EndTime)       MaxEndTime,
           DATEDIFF(second, MIN(apsl.StartTime), MAX(apsl.EndTime)) AS 
           ElapsedSeconds
    FROM   AsyncProcessStatusLog apsl

    The output will be: 

    MinStartTime

    MaxEndTIme

    ElapsedSeconds

    2013-02-05 12:00:58.287

    2103-02-05 12:01:38.283

    40

    It took only 40 seconds to finish.Thus we have saved 30 seconds and got almost 42% performance improvement. 

    Lesson Learned During from Real Life Implementation

    1. Please be 100% careful  about the following 2 exception inside the Stored Procedures code that you should consider for parallel execution. Because SQL Server TRY CATCH block does not honor this type of exception.

      •          Compile errors, such as syntax errors that prevent a batch from executing.
      •         Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution. 

      In this situation, the stored procedure's running ("R") state for ever and will never changes thus the whole process will hang on by WAIT State. You must have to manually kill the process to break situation then. 

    2. Please add an extra code block inside " Wait_Unitl_Chunk_ToBe_Finished_usp" stored procedure to break to loop after a defined time. Because if the system went into a long wait state for a long running query or any non recognized system exception mentioned above so that other query can have slot or resource to execute. 

    3. Have a look on the SQL Agent Job history and clean up any non deleted job(for exception) periodically. The  ExecuteSQL_ByAgentJob_usp stored procedures gives JobId as an output parameter you can save this to the metadata table for future reference to identify which job got exception and delete them automatically.  

    Conclusion

    Executing long running queries asynchronously not only save time but also ensure maximum usages of CPU and memory. On top of this, not running all queries at a time, running them in a smaller chunks also prevent the queries from occupying all system resources so that that the queries do not suffer from lack of resources. Thus the overall system runs in a managed and optimized fashion.  

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Database Developer The Jaxara IT Ltd.
    Bangladesh Bangladesh
    I have 10.5 years of cumulative professional experience in information technology. Specifically, in areas of database administration, development, system appreciation, requirement analysis and web development.

    Obtained following Microsoft Certifications:

    •Microsoft Certified IT Professional (Database Developer 2008)
    Microsoft Corporation, License D266-6721
    •Microsoft Certified Technology Specialist ( SQL Server 2008, Database Development)
    Microsoft Corporation, License C149-3291
    •Microsoft Certified Technology Specialist (SQL Server 2008, Implementation and Maintenance)
    Microsoft Corporation, License C149-3290

    http://lnkd.in/s2DveK

    Comments and Discussions

     
    QuestionHow to write The Stored Procedures/Statements for parallel execution Pin
    Member 141404656-Feb-19 23:38
    Member 141404656-Feb-19 23:38 
    QuestionCalling the long running Stored Procedure with multiple Parameters Pin
    pjeet_441125-Nov-13 19:41
    pjeet_441125-Nov-13 19:41 
    AnswerRe: Calling the long running Stored Procedure with multiple Parameters Pin
    Khorshed Alam, Dhaka27-Nov-13 17:28
    Khorshed Alam, Dhaka27-Nov-13 17:28 
    GeneralMy vote of 5 Pin
    magicpapacy15-Apr-13 20:20
    magicpapacy15-Apr-13 20:20 
    GeneralMy vote of 5 Pin
    DrewCopenhaver29-Mar-13 9:10
    professionalDrewCopenhaver29-Mar-13 9:10 
    GeneralRe: My vote of 5 Pin
    Khorshed Alam, Dhaka31-Mar-13 20:08
    Khorshed Alam, Dhaka31-Mar-13 20:08 
    GeneralWonderful Share... Pin
    Gulshan N25-Mar-13 8:54
    Gulshan N25-Mar-13 8:54 
    GeneralRe: Wonderful Share... Pin
    Khorshed Alam, Dhaka28-Mar-13 1:30
    Khorshed Alam, Dhaka28-Mar-13 1:30 
    GeneralMy vote of 5 Pin
    Dileep Mada22-Feb-13 22:41
    professionalDileep Mada22-Feb-13 22:41 
    GeneralRe: My vote of 5 Pin
    Khorshed Alam, Dhaka22-Feb-13 23:22
    Khorshed Alam, Dhaka22-Feb-13 23:22 
    GeneralMy vote of 5 Pin
    John B Oliver22-Feb-13 0:26
    John B Oliver22-Feb-13 0:26 
    GeneralRe: My vote of 5 Pin
    Khorshed Alam, Dhaka22-Feb-13 0:29
    Khorshed Alam, Dhaka22-Feb-13 0:29 
    SuggestionWhy don't you use SSIS Pin
    JanvdKruyk18-Feb-13 9:05
    JanvdKruyk18-Feb-13 9:05 
    GeneralRe: Why don't you use SSIS Pin
    Khorshed Alam, Dhaka18-Feb-13 15:53
    Khorshed Alam, Dhaka18-Feb-13 15:53 
    GeneralMy vote of 5 Pin
    Sk. Tajbir17-Feb-13 4:37
    Sk. Tajbir17-Feb-13 4:37 
    GeneralRe: My vote of 5 Pin
    Khorshed Alam, Dhaka17-Feb-13 15:45
    Khorshed Alam, Dhaka17-Feb-13 15:45 
    QuestionUseful article Pin
    Win32nipuh17-Feb-13 2:39
    professionalWin32nipuh17-Feb-13 2:39 
    AnswerRe: Useful article Pin
    Khorshed Alam, Dhaka17-Feb-13 17:46
    Khorshed Alam, Dhaka17-Feb-13 17:46 
    SuggestionA typo... Pin
    pt140116-Feb-13 21:39
    pt140116-Feb-13 21:39 
    GeneralRe: A typo... Pin
    Khorshed Alam, Dhaka17-Feb-13 1:25
    Khorshed Alam, Dhaka17-Feb-13 1:25 
    GeneralMy vote of 5 Pin
    Member 968190612-Feb-13 8:40
    Member 968190612-Feb-13 8:40 
    very nice!!!
    GeneralRe: My vote of 5 Pin
    Khorshed Alam, Dhaka12-Feb-13 15:46
    Khorshed Alam, Dhaka12-Feb-13 15:46 
    GeneralHelpful Pin
    SERokon11-Feb-13 16:25
    SERokon11-Feb-13 16:25 
    GeneralRe: Helpful Pin
    Khorshed Alam, Dhaka11-Feb-13 16:33
    Khorshed Alam, Dhaka11-Feb-13 16:33 
    QuestionMy vote is 5 Pin
    Rokibol10-Feb-13 19:15
    Rokibol10-Feb-13 19:15 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.