Post reply

How used space in transaction log is calculated and where is the glitch?

  • March 21, 2012 at 3:35 am

    #254997

    Hello!

    We all know about DBCC SQLPERF(LOGSPACE) and that we can use it to see Log Space Used in %%. But HOW this %% is calculated?? Lets do the test:

    USE master

    go

    CREATE DATABASE DB123

    ON PRIMARY (

    NAME = 'DB123_Data',

    FILENAME = 'c:\_test_\1d.mdf',

    SIZE = 5 MB,

    MAXSIZE = 5 MB )

    LOG ON (

    NAME = 'DB123_Log',

    FILENAME = 'c:\_test_\1l.ldf',

    SIZE = 1 MB,

    MAXSIZE = 1 MB )

    GO

    USE DB123

    GO

    DBCC SQLPERF(LOGSPACE)

    DBCC LOGINFO

    --clean up

    USE master

    go

    DROP DATABASE DB123

    The SQLPERF report:

    Database NameLog Size (MB)Log Space Used (%)

    DB1230.992187529.6752

    The number 0.9921875Mb=0.9921875 Byte*1024*1024=1040384 byte=1Mb - 8192 byte (log file header)=EXACTLY!

    The number 29.67% as used space... Hmmm.. May be, lets check it. The 29.67% from 1040384 byte =10403.84 * 29.67=308681.9328~308682 bytes.

    But! What tell us the command DBCC LOGINFO? It tells:

    [font="Courier New"]FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN

    22539528192 662640

    22539522621440000

    22539525160960000

    22785287700480000

    [/font]

    The column FSeqNo quite explicit on the point of used space: 253952 bytes - used, all remains - free. OK, just for case, add log file header as (obvious) also used: 253952 + 8192 = 262144 <- that is ABSOLUTELY MAXIMUM number of bytes occupied by records of log file right now!

    And so, the question is: how SQLPERF find 308682 bytes used, while there is only 262144? And difference is NOT inconsiderable - around 50 KB! It is considerably for so tiny log file (1MB).

  • March 22, 2012 at 12:41 am

    #1462526

    No ideas??

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

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