Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Answered: Bulk delete in chunks

    I am using the code below to delete records from a large table. But I keep getting a error.

    Started: 10:50:40 AM
    Error: 2016-08-09 10:52:14.82 Code: 0x00000000
    Source: Purge Old Data

    Description: Delete Trace Returns Aug 9 201 End Error
    Error: 2016-08-09 10:52:14.82 Code: 0xC002F210
    Source: Purge Old Data Execute SQL Task
    Description: Executing the query "EXEC Cleanup_FULF" failed with the following error: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0<c/> current count = 2.".
    Possible failure reasons:
    Problems with the query<c/>
    "ResultSet" property not set correctly<c/>
    parameters not set correctly<c/>
    or connection not established correctly. End Error
    DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:50:40 AM Finished: 10:52:14 AM Elapsed: 94.069 seconds. The package execution failed. The step failed.

    Code:
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    DECLARE @dd Date;
    SET @dd = DATEADD(week,-52,GETDATE()) 
     
    DECLARE @Job varchar(4);   
    DECLARE @r INT;
    DECLARE @STRMSG varchar(80);
    
    SET @STRMSG = N'FULF Clenup for records older than ' + RTRIM(CAST(@dd AS nvarchar(10))) 
    	+ N' Started ' + RTRIM(CAST(GETDATE() AS nvarchar(30)))   
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    
    /* ************************************** */
    /* Delete record from Trace Return Table  */
    /* ************************************** */
    SET @r = 1;
    WHILE @r > 0
    	BEGIN TRY
    	  BEGIN TRANSACTION;
    	 
    	  DELETE TOP (100000) 
    		FROM IMBTracing.dbo.Trace_Return_Table 
    		WHERE FileType = 'FULF' and AccumDate <= @dd;
    	 
    	  SET @r = @@ROWCOUNT;
    	  CHECKPOINT;
    	END TRY
    	BEGIN CATCH
    		SET @STRMSG = (SELECT ERROR_MESSAGE())
    		RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
            IF @@TRANCOUNT>0 ROLLBACK	
    	END CATCH
    
    SET @STRMSG = N'Delete Trace Returns ' 
    	+ RTRIM(CAST(GETDATE() AS nvarchar(10)))  
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

  2. Best Answer
    Posted by MCrowley

    "COMMIT is required when you have a BEGIN TRANSACTION statement even in simple recovery mode. Add the COMMIT (before the CHECKPOINT), and the problem will go away."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Did you mean COMMIT, instead of CHECKPOINT?

    COMMIT will commit the changes started by the BEGIN TRANSACTION statement.

    CHECKPOINT will flush the transaction log (where possible), and write dirty pages from memory to the disk.

  4. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    This is a Simple recovery model database so I don't believe Commit will work. The database is SS 2008 R2. I am using a method I found here http://sqlperformance.com/2013/03/io.../chunk-deletes. I think the logic is sound for not growing the log and the test Aaron Bertrand preformed indicate little loss in performance. Perhaps I didn't code the SQL correctly for a stored procedure. When I ran it stand alone it did give me a message that there were uncommitted transactions did I want to commit them... Could that be what is causing this to not work? Is there a way to make it work, or a better way? Some have suggested getting rid of the Begin Transaction - CHECKPOINT as I said I was trying to avoid the log growth and need to do it separately later.
    Last edited by wjburke2; 08-09-16 at 15:42.

  5. #4
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    I have tried SET IMPLICIT_TRANSACTIONS is OFF. Moving the CHECKPOINT before the @@ROWCOUNT. IF @@TTRANCOUNT>0 CHECKPOINT; At this point I am just grasping at straws.
    Last edited by wjburke2; 08-09-16 at 15:45.

  6. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    COMMIT is required when you have a BEGIN TRANSACTION statement even in simple recovery mode. Add the COMMIT (before the CHECKPOINT), and the problem will go away.

  7. #6
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Yep, that worked. Thank you for your answers. I have been at this all day and was about to give up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •