2

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 < @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?

  • The log will increase for other operations that get stuck because of the update too, so it's not directly proportional to the amount of records or data the update does. I've seen databases of 50gb raising logs of 360b. – EzLo Aug 13 '18 at 14:07
  • If it's extremely interesting for you and you have much enough free time you could try to execute some short transactions read log information and then get some estimate values. Perhaps this answer would help this way dba.stackexchange.com/questions/82046/… – Denis Rubashkin Aug 13 '18 at 14:26
  • As a side note (triggered by my OCD), do not put random tables in msdb even on a test system. it's not the best practice. Use tempdb or dedicated database for messing around. – Marcin Gminski Aug 13 '18 at 19:59
  • What version of SQL Server are you using? – aduguid Aug 14 '18 at 3:52
  • @aduguida - for the test above it was 2017 on the most current CU, is there a big change you know about between some versions? – Bartosz X Aug 15 '18 at 8:24
4

Is there any way to calculate such an estimation?

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

  • With all of the respect - doing an isolated transaction on a smaller batch + checking the page content via DBCC PAGE for the information as shown above is "some" way is predicting it. Agree - would be nicer once closed inside a single script, but I thought that there is something "build in" I didn't know. I will update it later once I create a simple SP for that matter. Kind regards – Bartosz X Aug 13 '18 at 15:04
  • Remember that dbcc page is undocumented and so is fn_dblog. So I have not recommended. – Kin Shah Aug 13 '18 at 15:20
  • @BartoszX Even if you use DBCC page and a smaller size, there is no guarantee that the same logging will take place. Some might be before/after images, some might be row level, etc. So if "It's better than nothing" is what you're after, then just pick a random number - it's better than nothing. Kin is correct, there is no way to predict with any amount of accuracy or precision. – Sean Gallardy Aug 13 '18 at 16:25
2

This should only be used for educational purposes. See Kin's comment about potential risk of runnig fn_dblog

This will probably not answer your question as there is no easy answer but it will (hopefully) put you in the right direction to understand what happens in the SQL Server Transaction Log and why it's not always easy to estimate its size.

Create a new TEST database in FULL recovery model and LOG growth to 1MB (so we have a good growth resolution) and run the following:

USE [TEST]
GO

/* set recovery model to full so we retain transactions in the log */
ALTER DATABASE [TEST] SET RECOVERY FULL WITH NO_WAIT
GO

/* prepare enviroment */
IF OBJECT_ID('tempdb..#logspace') IS NOT NULL
    DROP TABLE #logspace 

IF OBJECT_ID('tempdb..#dblog') IS NOT NULL
    DROP TABLE #dblog

IF OBJECT_ID('dbo.test') IS NOT NULL
    DROP TABLE dbo.test
GO

CREATE TABLE #logspace (
    [Database Name] sysname, 
    [Log Size (MB)] float,
    [Log Space Used (%)] float,
    [Status] bit
    )

/* shrink the log to 1MB */
DBCC SHRINKFILE (N'TEST_log' , 1)
GO

/* first log dump, this shold have just few records
CAREFUL fn_dblog is not documented! */
select *, step=1 
into #dblog
from fn_dblog(null, null) 

/* record log size, it will have 1MB */
INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE)')
GO

/* dump some data into our TEST database. 
   the below will create ~21MB table with 98692 rows */
begin transaction
    SELECT *, tcol=0
    into dbo.test
    from sys.messages

    /* get log size again, wonder how much data will be in the log
       if we have just created 21MB table? should be around 21MB +
       some extra stuff */
    INSERT INTO #logspace
    EXEC ('DBCC SQLPERF(LOGSPACE)')
    GO

    /* second dump of the log after table creation */
    insert into #dblog
    select *, step=2
    from fn_dblog(null, null) 
    GO
commit transaction

/* switching database in SIMPLE mode will clear down the log.
   this is just me being lazy but it will prove the point */
ALTER DATABASE [TEST] SET RECOVERY SIMPLE WITH NO_WAIT
GO

/* shrink log again  to 1MB */
DBCC SHRINKFILE (N'TEST_log' , 1)
GO

/* 3rd dump of the log after switching to SMIPLE model. 
   log should be empty-ish */
insert into #dblog
select *, step=3
from fn_dblog(null, null) 

/* record log size, shold be 1MB */
INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE)')
GO

/* now run update statement */
begin transaction
    UPDATE dbo.test
    SET tcol+=1

    /* record log size right after the update statement */
    INSERT INTO #logspace
    EXEC ('DBCC SQLPERF(LOGSPACE)')
    GO

    /* fourth dump of the log after the update statement */
    insert into #dblog
    select *, step=4
    from fn_dblog(null, null) 
    GO
commit transaction

Now let's analyse what happened:

enter image description here

It tells me the following:

  1. The initial size of the log was 1MB
  2. The SELECT INTO statement recorded 24MB of information in the transaction log. This is close enough to the table size of 21MB.
  3. The log was shrunk back to 1MB
  4. The subsequent UPDATE recorded 46MB of data in the transaction log which is almost double the table size!

Now let's check what transactions we have recorded. Let's focus on step 4 - the UPDATE statement:

Running the following:

select * 
from #dblog
where step = 4
and AllocUnitName = 'dbo.test'

Gives us exactly the number of updates rows:

enter image description here

However, step 4 contains a few more rows not directly related to dbo.test:

select count(*)
from #dblog
where step = 4

Gives us 98871

Now dump the #dblog table to physical so we can check the size of step 4

select *
into dbo.dblog_step4
from #dblog
where step = 4

which is 87MB of data! -- nowhere close to the 46MB of the log size.

enter image description here

Does this answer your question? probably not, does it help to understand what happens in the transaction log? I hope so. Does it make it clear why it's not easy to estimate the impact on the tlog? I hope so..

The best approach is to test test test test test test.....estimate UPDATE/DELETE/INSERT impact in a non-production environment and depending on results batch up the transaction or adjust production log size.

  • This is good info, but fn_dblog(null, null) should be used for educational purpose (definitely not on PROD env). Also depending on version of sql server, this can be dangerous <-- see the note. Doing a select * on fn_dblog will be very expensive. – Kin Shah Aug 13 '18 at 18:56
  • @Kin thank you for the link, I wasn’t aware of this. – Marcin Gminski Aug 13 '18 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.