April 5, 2012 at 10:12 am
was running a purge process against a large database. The log file location does not have near enough room to do it in a single batch. So I setup loop in a procedure that will select a day at a time.
Each delete statement starts with a Begin Tran and ends with a commit. then the day is incremented the parameters set and another transaction to delete the next day....
i started this and it was running fine, later I went back and I'm getting log file full errors, it saying to check the log reuse wait.
the wait value is 3 and the desc is "ACTIVE_BACKUP_OR_RESTORE"
Before I started my process I did a full backup of the db, but it was complete. Aside from that there were no backup or recovery operations.
Any idea why that would be there and still be there now?
how does this get changed to 0?
April 5, 2012 at 10:17 am
Any possibility of a scheduled backup or someone else running a manual backup?
April 5, 2012 at 10:20 am
well I'm assuming that is what it said last night but not sure, there was a differential scheduled for 4am today. but I can guarantee there is no backup/recovery happening right now.
April 5, 2012 at 10:21 am
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 10:21 am
Possible to see your code?
April 5, 2012 at 10:29 am
here is the proc that does the deletes:
I'm mostly curious why the log wait is indicating a backup /recovery is in progress. For all I know it's said that for the last year...
ALTER PROCEDURE [dbo].[PurgeProduction] @RetainDays INT = 90 -- default if no value specified
AS
DECLARE @MinDateToDelete DATETIME
,@MaxDateToDelete DATETIME
,@CurrentDeleteDate DATETIME
,@Operation VARCHAR(200)
-- declare error handing variables
DECLARE @ErrorMessage NVARCHAR(4000) ;
DECLARE @ErrorSeverity INT ;
DECLARE @ErrorState INT ;
SET @MinDateToDelete = CAST(FLOOR(CAST((SELECT MIN(msg_dts)
FROM mydb.dbo.SMQUEUE
) AS FLOAT)) AS DATETIME)
SET @MaxDateToDelete = CAST(FLOOR(CAST(DATEADD(dd, (@RetainDays * -1) - 1, GETUTCDATE()) AS FLOAT)) AS DATETIME)
SET @CurrentDeleteDate = @MinDateToDelete
WHILE @CurrentDeleteDate <= @MaxDateToDelete
BEGIN
BEGIN TRY
SET @Operation = 'Deleting Production data for ' + CAST(@CurrentDeleteDate AS VARCHAR(20))
EXEC dbo.InsertLog
@Operation
BEGIN TRANSACTION
DELETE a
FROM mydb.dbo.SMQUEUE q
JOIN mydb.dbo.SMQADDR a ON q.SMQ_PK = a.SMQ_PK_LINK
WHERE q.MSG_DTS >= @CurrentDeleteDate
AND q.MSG_DTS < DATEADD(dd, 1, @CurrentDeleteDate)
DELETE FROM mydb.dbo.SMQUEUE
WHERE MSG_DTS >= @CurrentDeleteDate
AND MSG_DTS < DATEADD(dd, 1, @CurrentDeleteDate)
COMMIT TRANSACTION
SET @Operation = 'Production data for ' + CAST(@CurrentDeleteDate AS VARCHAR(20))
+ ' deleted successfully'
EXEC dbo.InsertLog
@Operation
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @Operation = 'Transaction Failed, Production data for, '
+ CAST(@CurrentDeleteDate AS VARCHAR(20)) + ', was not purged'
EXEC dbo.InsertLog @Operation
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE() ;
-- raiseerror will kill procedure (batch was rolled back at beginning of catch block)
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
) ;
END CATCH ;
-- increment to next day
SET @CurrentDeleteDate = DATEADD(dd, 1, @CurrentDeleteDate)
END
April 5, 2012 at 10:32 am
That doesn't necessarily reflect the immediate situation (and no, it could not have read that for the last year, unless your log has been growing for a year)
If means that a full or diff backup was preventing log truncation last time it was checked. Run a checkpoint, that should update the log reuse to what is currently preventing log truncation.
That said, if it says that an active backup was preventing log truncation then there was a full or diff backup in progress that was preventing log truncation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 10:36 am
ok I ran the checkpoint and log reuse is 0 now
Also I tried to run a shrink this morning and it failed, guessing that was also due to the log reuse.
shrink works now and release 98% of the log space
April 5, 2012 at 10:39 am
Okay, I'm at a loss right now. I know the title says the database is using the simple recovery model, but just to be sure could you verify that it hasn't been changed?
The code looks good, though there are a few things I would change that would have no direct affect on what it does.
Could there be any long running transactions that may be keeping the VLF's from being cleared on checkpoint? Unfortunately, I don't have any code handy for checking this.
April 5, 2012 at 10:45 am
Couple of posts made while I wrote mine. You might want to add a manual checkpoint after the commit transaction.
April 5, 2012 at 1:05 pm
would adding a manual checkpoint after each batch slow things down considerably?
Also please suggest any code changes if you want to take the time.
April 5, 2012 at 1:14 pm
Tom Van Harpen (4/5/2012)
would adding a manual checkpoint after each batch slow things down considerably?Also please suggest any code changes if you want to take the time.
Maybe this evening. Some would consider them cosmetic in nature, doing the same thing you are currently doing, just differently.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply