September 27, 2009 at 6:38 pm
Regardless whether your database is in Simple or Full recovery the log tracks update, insert, delete statements.
The actual delete statement was not complete or had an extraneous quote character so the below statement carries that forward as I'm not exactly sure what the statement should be. By looping with some value (below it is defined as 100K) the log for each purge of 100K or less rows should be reduced but in your situation you may need to reduce or increase the value. In Full recovery you would still need to backup the log often and possibly during the looping. I've also seen in Simple a truncate log statement during the looping. you will need to test for your situation whether that is required.
One last comment if you have an index on TF.StartTime you are most likely not using the index. You are better off changing the logic to have only TF.StartTime on one side and the calculated value on the other.
DECLARE @Purged_Count INT -- Number that were processed
DECLARE @Loop_Count INT -- Number of rows to process each loop
SET @Purged_Count = 1
SET @Loop_Count = 100000
WHILE (@Purged_Count) ' + cast(@Time as
varchar)
SET @Purged_Count = @@ROWCOUNT
END
September 28, 2009 at 3:49 am
You've already asked this question here
http://www.sqlservercentral.com/Forums/Topic793661-338-1.aspx
and here
http://www.sqlservercentral.com/Forums/Topic793659-338-1.aspx
and had responses on both... what else are you looking for?
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply