Estimate transaction log size for single transaction Today I have tried to execute an update operation on 50M+ rows, something like:

UPDATE [table] SET [Column] += 1;


after 30min of waiting I have seen that the log has started to filling itself, after another 30min the entire transaction rolled-back due to the log being full.
DB was in the simple recovery mode but this is meaningless as this was done as a single transaction. SQL Server on it's own couldn't predict that it will use the entire log size (lets say that it was locked size for this matter), but my question to you my dear experienced DBA's - could you predict that size? Is there any way to calculate such an estimation?


  UPDATE:


Yes, I understand the process, I know I can batch, I know how to make it performing well also. My real question is how big the log size is going to be?
How would you calculate update on a simple table like:

CREATE TABLE [TABL] ([ID] int identity(1,1) PRIMARY KEY CLUSTERED, [X] int not null);

INSERT INTO [TABL] VALUES(1)
GO 1000

UPDATE [TABL]
SET [X] = 2;


On my test box, the update part of the operation was 6 pages large = 48KB.
When I repeated the same for 10k rows I received 144 pages of transaction log.
For 100k rows it was 1314 pages, for 1M it was 13970 pages. This shows that we can concider this as linear function (on the we have extra pages which has to be there anyway no matter did we update anything or not --> 2-3 pages).

Going back to the begeining I know that I could run a single batch on 5% of my total operation, check the page chenge content (as blogged by Paul Randal):

    DECLARE @Extent_ID              INT;
    DECLARE @Size_Total             BIGINT = 0;
    DECLARE @File_ID                INT;
    DECLARE @File_Size_Pages        INT;

    DECLARE @Log_Page_ID            INT;
    DECLARE @Log_Total              BIGINT = 0;
    DECLARE @Log_Total_Changed      BIGINT;
    DECLARE @DBCC_PAGE_String_Log   VARCHAR (200);




    DECLARE [files] CURSOR FOR

        SELECT [file_id], [size] 
        FROM master.sys.master_files
        WHERE [type_desc] = N'ROWS'
        AND [state_desc] = N'ONLINE'
        AND [database_id] = DB_ID(@DB_Name);

    OPEN files;

    FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Extent_ID = 0;
        SET @Size_Total += @File_Size_Pages / 8;    --| Convert size in pages to to extents

        WHILE (@Extent_ID &lt; @File_Size_Pages)
        BEGIN

             SELECT @Log_Page_ID = @Extent_ID + 7;

             SELECT @DBCC_PAGE_String_Log = 'DBCC PAGE ([' + @DB_Name + '], ' + CAST (@File_ID AS VARCHAR) + ', ' + CAST (@Log_Page_ID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

             TRUNCATE TABLE [msdb].[dbo].[DBCC_Page_Check];
             INSERT INTO [msdb].[dbo].[DBCC_Page_Check] EXEC (@DBCC_PAGE_String_Log);

             SELECT @Log_Total_Changed = SUM ([msdb].[dbo].[Extent_Change_Checks] ([Field]))
             FROM [msdb].[dbo].[DBCC_Page_Check]
             WHERE [VALUE] = '    MIN_LOGGED'
             AND [ParentObject] LIKE 'ML_MAP%';

             SET @Log_Total += @Log_Total_Changed;

             SET @Extent_ID += 511232;
      END
----------------------------------------------------------------------------------------------------------------------------

        FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;
    END;

    DROP TABLE [msdb].[dbo].[DBCC_Page_Check];
    CLOSE [files];
    DEALLOCATE [files];


but this still requires to make a change (even with rollback) before we could estimate anything at all. So again - is there anyone who knows how to estimate it better than the way above - up front?
Is there any way to calculate such an estimation?


No. You cannot predict that. You should always do your updates or deletes in batches.