Transaction log used space

  • It's a little weird for me. I have a database (AdventureWorks2014; SQL Server 2014) in full recovery model.

    Executing this script 3 times:

    declare @i int = 0
    declare @date datetime

    while @i <= 1000
    set @date = getdate()

    begin tran
    update Production.TransactionHistory
    set TransactionDate = getdate(),
    ActualCost = @i + TransactionID
    where TransactionID % 3 = 0

    commit tran

    set @i = @i + 1

    returns these results for the log file space usage:

    select * from sys.dm_db_log_space_usage

    12 4280279040 2110091264 49,29799 2101624832
    12 4280279040 2120466432 49,54038 2101067776
    12 4280279040 1261416448 29,47042 1238355968

    I suspect each execution of script should increase log file usage / log file size in full recovery model... Why the first time I have 49% usage and the the third only 29% heaving the same total file size?

    Of course there is no log backup...


  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Have you taken a full backup of the database since it was placed in the Full Recovery Model? Until the first full backup completes, the transaction log acts as if the database were in the Simple Recovery Model. Transaction log backups have no value until there is at least one Full backup because those log backups can't be used to recover anything.

    Take a Full backup of the database and try your test again.


    Eddie Wuerch
    MCM: SQL

  • that was it, thx

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply