Delete Stored Procedure - Log file growing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Delete Stored Procedure - Log file growing

Author

Topic

TXAggie00
Starting Member

8 Posts

Posted - 2014-11-26 : 13:48:01

We have ETL jobs that will sometimes need to do a full refresh from the Source System to the EDW. We cannot truncate the tables, since there are other source systems loaded, so we need to do our deletes with a filter on source system. We have written a stored procedure to do this so we can manage it better. We have it looping through the deletes, committing every delete top (10000). Unfortunately, we are still seeing the log file grow. Here is the main part of the loop:

WHILE(@RowCount > 0)
  BEGIN
    BEGIN TRANSACTION
      SET @SQLString = N'DELETE TOP ('+ CAST(@DeletesPerCommit AS VARCHAR(10)) +') 
          FROM ' + @EdwTable + ' WHERE RDSOURCENUMID = ' + CAST(@RdSourceNum AS VARCHAR(10))
      BEGIN TRY
        EXEC(@SQLString)
        SET @RowCount = @@ROWCOUNT
      END TRY
      BEGIN CATCH
        ROLLBACK
        SET @ErrorMessage = ERROR_MESSAGE()
        RAISERROR('ERROR: %s',18,-1,@ErrorMessage)
        RETURN -1
      END CATCH
    COMMIT
    SET @RecordsDeleted = @RecordsDeleted + @RowCount
    IF(@RecordsDeleted >= 200000) BREAK
  END	

RETURN @RecordsDeleted

I am an ETL developer not a DBA, so I can't pretend to know anything about the inner workings of SQL Server 2012. In the code above, we have put a break in after 200000 records are deleted. This works fine. Deletes occur, no log growth, minimal impact on resources. If we remove the break on tables that have have > ~2 million records, we see no count change and log file grows. No issue with smaller tables. I was under the impression that once we commit the transaction, the log file clears the chunk of data written to it. We have all the DBs set to Simple Recovery. Does anyone see what the issue is?

Also, not sure this matters, but we have tried adding a

WAITFOR DELAY '00:00:05'

after the commit thinking we needed to give it time to fully commit and clear the data written to the log, but it had no impact.

Thanks,
Scott

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-26 : 14:28:01

Move the BEGIN TRAN/COMMIT TRAN into the TRY section.

BEGIN TRY
BEGIN TRAN
...
COMMIT TRAN
END TRY

You should also be checking XACT_STATE() when committing and rolling back:

IF XACT_STATE() = 1 COMMIT TRANSACTION;

IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;

You could be encountering a constraint violation that makes the transaction uncommitable.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-11-26 : 14:46:48

Thanks Tara. I will try that. I have it running right now and everything seems good. I removed the BREAK and in it's place added

CHECKPOINT 10

That seems to be doing the trick. There are no foreign keys in our EDW. Data integrity is confirmed in the ETL process, so shouldn't have any constraint issues. Let me ask you another question. Should I leave ROLLBACK in the CATCH and if so, does it matter that the ROLLBACK is outside the transaction?

Maybe you can explain to me why adding CHECKPOINT was (or at least seems to be at this point) the magical answer. Still ~1 million to delete. ~2 million deleted in 30 mins (this table has quite a few indexes on it).

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-26 : 14:53:01

Yes ROLLBACK should be left in the CATCH. I'm suggesting that XACT_STATE() should be added to it, still in the CATCH. The ROLLBACK is still inside the transaction. The CATCH occurs within it.

If CHECKPOINT resolved the issue, then you must not have automatic checkpoints occurring very often. I suspect this is the case because it's an ETL server where you likely have data changes occurring in a window but not all day long like you would with an OLTP database.

It's a complicated topic:
http://msdn.microsoft.com/en-us/library/ms189573.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-11-26 : 15:05:22

That makes sense, Tara. Thanks again. So how do I handle the COMMIT if the XACT_STATE() returns -1? Shouldn't the catch handle that? I would imagine since we are in a transaction block, it would never return 0.

Here is the loop now:

WHILE(@RowCount > 0)
  BEGIN
    SET @SQLString = N'DELETE TOP ('+ CAST(@DeletesPerCommit AS VARCHAR(10)) +') 
	                 FROM ' + @EdwTable + ' WHERE RDSOURCENUMID = ' + CAST(@RdSourceNum AS VARCHAR(10))
    BEGIN TRY
      BEGIN TRANSACTION
        EXEC(@SQLString)
        SET @RowCount = @@ROWCOUNT
      IF XACT_STATE() = 1 COMMIT TRANSACTION
      ELSE PRINT 'What am I to do here?'
    END TRY
    BEGIN CATCH
      IF (XACT_STATE()) <> 0 ROLLBACK
      ELSE PRINT 'or here?'
      SET @ErrorMessage = ERROR_MESSAGE()
      RAISERROR('ERROR: %s',18,-1,@ErrorMessage)
      RETURN -1
    END CATCH
    SET @RecordsDeleted = @RecordsDeleted + @RowCount
    IF(@RecordsDeleted >= 200000) CHECKPOINT 10
  END	

RETURN @RecordsDeleted

Thanks,
Scott

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-26 : 15:15:19

I don't think it matters in your case since there aren't any constraints. See Example C for the code and some explanation regarding XACT_STATE: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Regarding what to do in the "What am I to do here" section, there isn't anything to do. The transaction no longer exists, so there is no ELSE needed.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-11-26 : 15:23:22

Thanks for all your help Tara! I sure do appreciate your patience and time! I am going to post the code as it stands now in case it helps anyone else.

WHILE(@RowCount > 0)
  BEGIN
    SET @SQLString = N'DELETE TOP ('+ CAST(@DeletesPerCommit AS VARCHAR(10)) +') 
	                 FROM ' + @EdwTable + ' WHERE RDSOURCENUMID = ' + CAST(@RdSourceNum AS VARCHAR(10))
    BEGIN TRY
      BEGIN TRANSACTION
        EXEC(@SQLString)
        SET @RowCount = @@ROWCOUNT
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      SET @ErrorMessage = ERROR_MESSAGE()
      IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION
      IF (XACT_STATE()) = 1 COMMIT TRANSACTION
      RAISERROR('ERROR: %s',18,-1,@ErrorMessage)
      RETURN -1
    END CATCH
    SET @RecordsDeleted = @RecordsDeleted + @RowCount
    IF(@RecordsDeleted >= 200000) CHECKPOINT 10
  END	

RETURN @RecordsDeleted

Thanks,
Scott

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-26 : 15:28:52

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-12-03 : 12:08:06

Just when you thought everything was good...

So I think I know what is going on. The stored procedure works flawlessly. The problem is when it is called from our ETL. It seems that the ETL tool (BODS) is wrapping the call to the stored procedure in its own transaction. Because of this, the transaction is our stored procedure is being ignored (at least that is what it seems to be doing). So our large tables are still filling up the log file. I have tried adding

SET IMPLICIT_TRANSACTIONS OFF

at the beginning of the stored procedure, but that had no affect and the outcome was the same. Any suggestions on how to circumvent this?

Thanks,
Scott

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 12:15:16

I don't think you can resolve that in SQL Server. You'll need to investigate the ETL tool to see if you can turn it off. Alternatively, can you run it in a SQL Agent job instead?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-12-03 : 12:21:54

quote:
Originally posted by tkizer

I don't think you can resolve that in SQL Server. You'll need to investigate the ETL tool to see if you can turn it off. Alternatively, can you run it in a SQL Agent job instead?

Thanks Tara. I have a ticket created for BODS to see if that option is even available, but could you please explain how I could run a SQL Agent job? Can this be kicked off through a stored procedure? Can you pass it parameters?

Thanks (again!),
Scott

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 12:30:57

Yes you can kickoff a job through a stored procedure, though special permissions will be needed. sp_start_job is what you'd run to kick it off. It is an asynchronous call though. Passing it parameters would be tricky though. I was thinking your stored proc had a static set of parameters.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-12-03 : 13:53:07

Thanks Tara. So it sounds like there is no workaround for nested explicit transactions? I am not sure an asynchronous process would be beneficial since our ETL is dependent on the deletes finishing prior to moving onto the load process. We can implement some logic to check the counts before it moves on, but the work effort would be enormous and I think if we are going to have to go that route, we would just implement the looping logic on our side and push down the deletes to the database.

Thanks,
Scott

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 14:00:52

sp_start_job would be asyncronous, but you can query the job history to determine if it finished. We use a "start and wait" stored procedure that calls sp_start_job and then keeps checking the job history before completing the "start and wait" stored procedure. By doing this, it's now synchronous.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TXAggie00
Starting Member

8 Posts

Posted - 2014-12-03 : 17:09:43

Okay, I think we found a solution. We added the following at the beginning of the stored procedure

DECLARE @TranCount int;
SET @TranCount = @@TRANCOUNT
IF(@TranCount > 0) ROLLBACK

and added the following to the end of the stored procedure

IF(@TranCount > 0) BEGIN TRAN

This basically closed the transaction that BODS started (sometimes it didn't start one. Weird). We had to add the part at the end because it would complain about transaction count mismatching.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 17:12:35

Sounds like a good workaround.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources