Want to find transaction log size before taking backup

  • HI,

    I want to estimate the transaction log size before taking backup not the used space

  • Do you mean the size of the backup file it will create?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • i need the size of the backup file it will create

  • CREATE TABLE #LOGSPACE

    (

    DB SYSNAME,

    LogSize FLOAT,

    SpaceUsed FLOAT,

    Stat BIT

    );

    INSERT INTO #LOGSPACE

    (

    DB,

    LogSize,

    SpaceUsed,

    Stat

    )

    EXEC (

    'DBCC SQLPERF (LOGSPACE)'

    );

    SELECT DB,

    LogSize,

    SpaceUsed,

    LogSize * (SpaceUsed / 100.0) AS SpaceUsedMB, -- approximate log backup size

    Stat

    FROM #LOGSPACE;

    The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (9/25/2012)


    The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.

    Yes, but bear in mind that log backups could potentially be quite large

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/25/2012)


    opc.three (9/25/2012)


    The SpaceUsedMB could be low if you ever switch the DB into the Bulk Logged Recovery Model and perform any minimally logged operations.

    Yes, but bear in mind that log backups could potentially be quite large

    You're misunderstanding what I said. The number from the query (SpaceUsedMB) could be lower than the actual backup size if bulk logged was used, i.e. log space used would only count the extents, not the actual data which would come from the data file.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (9/25/2012)


    What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.

    Capacity planning?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (9/25/2012)


    Jeff Moden (9/25/2012)


    What I'd like to know is.... why does someone need to know what size the backup is? SQL Server figures that out and if there's not enough space for the backup, it'll throw an error before the backup actually starts.

    Capacity planning?

    Maybe but I still don't see why even that would be a problem. If the database exists well enough to have a log file to take a guess at, then just do a log backup and see how big it is. It's something that should be occuring anyway.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • opc.three (9/25/2012)


    CREATE TABLE #LOGSPACE

    (

    DB SYSNAME,

    LogSize FLOAT,

    SpaceUsed FLOAT,

    Stat BIT

    );

    INSERT INTO #LOGSPACE

    (

    DB,

    LogSize,

    SpaceUsed,

    Stat

    )

    EXEC (

    'DBCC SQLPERF (LOGSPACE)'

    );

    SELECT DB,

    LogSize,

    SpaceUsed,

    LogSize * (SpaceUsed / 100.0) AS SpaceUsedMB, -- approximate log backup size

    Stat

    FROM #LOGSPACE;

    Also that's a rather convoluted way of getting space used, I prefer

    Use yourdb

    GO

    Select name, size / 128,

    fileproperty(name, 'spaceused') / 128

    From sys.database_files

    Where type = 1 -- tlog files only

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Heh, that looks nicer. I'll have to try it out. Maybe I'll update my toolkit-script. Thanks.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • You can also check the performance counters too:

    SELECT d.name, logsize.cntr_value AS LogSize, logused.cntr_value AS PercentLogUsed

    FROM sys.databases d

    LEFT JOIN sys.dm_os_performance_counters logsize ON logsize.counter_name = 'Log File(s) Size (KB)' and logsize.instance_name = d.name

    LEFT JOIN sys.dm_os_performance_counters logused ON logused.counter_name = 'Percent Log Used' and logused.instance_name = d.name

  • the script is giving the used space but i need to know the size of backup file.

    for example i have a log of 50 gb and it used space is 0.02 % , by running the script the value is 10 mb but size is 1 mb after backup

  • It's not an exact science but it should give you an idea. Why are you worried about the diff between a 1MB backup and 10MB space used? if you're that tight on space then you have bigger fish to fry. The only way to know is to take the backup.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • One other note. If you're compressing your backups then all estimates will be off because compression rates are data-dependent. In round numbers though I see about a 90% compression rate for most OLTP databases.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 19 total)

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