How to not KILL your TX log.

[JeanNiBee]

Hi

I work in an environment where we don't always have access to the cofigurations of our DB. That being said if we find ourselves in a situation where we are runing queries on 10's of millions of records (this happens daily), we might need to add space to the log or change the size of tempdb as the default installations we are given my not be sufficient. Unfortunately we are NOT always allowed to do this.

We've overcome this issue with this little tip I was shown, that is, 'IF' you don't mind running queries in batches.

This is just an example of how to do this and NOT an 'actual' script.

DECLARE @maxRowsToProcess int, @exitLoopFlag int

SELECT @maxRowsToProcess = 10000
SELECT @exitLoopFlag = 0

WHILE ( @exitLoopFlag = 0 )
BEGIN

    DELETE FROM someTable WHERE blah = blah            
    -- OR UPDATING.
    UPDATE someTable set someField = 'someValue'

    IF ( @@rowcount < @maxRowsToProcess )
    BEGIN
        SELECT @exitLoopFlag = 1
    END

    COMMIT TRAN
    CHECKPOINT

END

Hope this helps.

 

[markmar1]

Ive used this technique quite abit.... While at a large telecom (not to be mentioned, but used to start with M and end in I), we had a few tables that were in the range of 80-100million rows, and all of the sudden, a new column needed to be added, and required a default. Fun stuff but worked like a charm...

Heck of alot better than trying to be cute with single statement and blow your system apart....