Post reply

logsize

  • September 3, 2009 at 11:43 am

    #210405

    i created report or alert if database logspaceused crosses 90%

    then i am getting the alert with >100%

    how to resolve it.............

  • September 3, 2009 at 11:45 am

    #1048965

    why the log space is showing as more than 100 % used

  • September 3, 2009 at 11:55 am

    #1048974

    Could you provide us with the code that you are using to create this report?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 3, 2009 at 12:03 pm

    #1048983

    ...

  • September 3, 2009 at 12:58 pm

    #1049024

    any help pls.............

  • September 3, 2009 at 1:09 pm

    #1049030

    why the log space used is showing as more than 100 %

  • September 3, 2009 at 2:44 pm

    #1049089

    i got it...........

    i ran the dbcc updateusge...............then it resolved..........

    thanks................

  • September 3, 2009 at 6:51 pm

    #1049170

    charipg (9/3/2009)

    i got it...........

    i ran the dbcc updateusge...............then it resolved..........

    thanks................

    Cool. Thanks for the feedback. But, why did you erase your code? That was a part of the problem that people need to see in order to appreciate your good answer.

    --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)

  • September 3, 2009 at 7:20 pm

    #1049177

    This is quite impossible to assist w/o correct information. Members, please avoid this.

    -lk

  • September 3, 2009 at 7:45 pm

    #1049183

    luckysql.kinda (9/3/2009)

    This is quite impossible to assist w/o correct information. Members, please avoid this.

    -lk

    Heh... avoid what? He's already got his answer. 😉

    --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)

  • September 4, 2009 at 3:07 am

    #1049262

    The question appears to about how best to monitor log usage, and why the figure obtained from DBCC SQLPERF(LOGSPACE) sometimes goes over 100%.

    As for the first part, while you certainly can use DBCC, I find it annoying to have to use the odd-looking INSERT syntax in order to save the figures away somewhere. I also don't like the fact that any future change to the DBCC output would break my script. For reporting purposes, I use a query like the one shown below to record log usage.

    I'll come back to the second part of the question in a bit - I can't remember the exact details offhand (though it may be due to the fact that the reported file size is not a hard limit - the server can use extra log space in some circumstances - during recovery for example).

    Paul

    WITH LogData (database_name, measure, value) AS

    (

    SELECT instance_name,

    CASE counter_name

    WHEN N'Log File(s) Size (KB)' THEN N'Size'

    WHEN N'Log File(s) Used Size (KB)' THEN N'Used'

    END,

    cntr_value

    FROM sys.dm_os_performance_counters PC

    WHERE counter_name IN (N'Log File(s) Size (KB)', N'Log File(s) Used Size (KB)')

    AND instance_name NOT IN (N'_Total', N'mssqlsystemresource', N'')

    ),

    LogUsage AS

    (

    SELECT database_name,

    [Log Size MB] = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Size' THEN value ELSE 0 END) / 1024.0),

    [Log Used MB] = CONVERT(DEC(9,2), SUM(CASE WHEN measure = N'Used' THEN value ELSE 0 END) / 1024.0)

    FROM LogData

    GROUP BY

    database_name

    )

    SELECT database_name,

    [Log Size MB],

    [Log Used MB],

    [Log Used %] = CONVERT(DEC(9,2), (([Log Used MB] * 100.0) / [Log Size MB]))

    FROM LogUsage

    ORDER BY

    database_name ASC;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 4, 2009 at 6:49 am

    #1049328

    Jeff Moden (9/3/2009)

    luckysql.kinda (9/3/2009)

    This is quite impossible to assist w/o correct information. Members, please avoid this.

    -lk

    Heh... avoid what? He's already got his answer. 😉

    And who gave you the authority to dictate what should be avoided? Most of us that have been around awhile "kinda" 😉 know what to avoid and where to try and help when possible.

    -- You can't be late until you show up.

  • September 4, 2009 at 9:59 am

    #1049488

    The thing about the log space used being slightly greater than the total size of the log is actually quite interesting.

    Both DBCC SQLPERF and the performance counter method subtract header/allocation pages from the total log size. If we have an 8MB single log file, the log records start being written at offset 8192 (skipping the first 8K page) - leaving 8192KB - 8KB = 8184KB for the log. Further allocation pages will exist at predictable intervals for larger log files (>4GB).

    edit: the current log file structure has just one management page right at the start of the file. This appears to be the source of information provided by the DBCC LOGINFO command. Since the key pointers are 64-bit, log files up to 32TB can be managed without the need for further pages. Each subsequent log structure also has a small header (96 bytes - same as for data pages). The first 8K 'page' of the log is unfortunately not readable with the DBCC PAGE command.

    This is why the physical file might be dead on 8MB, but SQLPERF reports very slightly under 8MB as the size of the log.

    The maths involved in calculating how full the log file is don't seem to be documented, but experimentation suggests that it is more of a very good estimate than an absolute, measured figure. When the 8MB log above is completely full, the space used is reported as about 4K over the 8MB. This is how the >100% problem comes about - space used seems to assume the whole 8MB is full, plus the mysterious 4K...

    I really wouldn't be concerned about it, however. It is a very small variance, especially for larger log files.

    During testing, I also managed to get SQL Server to expand my (zero auto-growth) 8MB log file during start-up recovery. So long as there is space on the disk, SQL Server may expand the log file beyond the set limit during recovery, to avoid having to mark the database as suspect due to running out of log space when it rolls back any uncommitted transactions (UNDO) during recovery.

    DBCC UPDATEUSAGE doesn't affect log files. I suspect that the log size changed for another reason (it may reduce in size when a transaction commits, for example).

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 4, 2009 at 10:13 am

    #1049499

    Paul White (9/4/2009)

    If we have an 8MB single log file, the log records start being written at offset 8192 (skipping the first 8K page) - leaving 8192KB - 8KB = 8184KB for the log. Further allocation pages will exist at predictable intervals for larger log files (>4GB).

    Where is that info from?

    Logs aren't divided into pages and they don't have allocation pages. They're divided internally into virtual log files (which do have headers) of varying sizes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 4, 2009 at 10:48 am

    #1049520

    GilaMonster (9/4/2009)

    Where is that info from?

    Logs aren't divided into pages and they don't have allocation pages. They're divided internally into virtual log files (which do have headers) of varying sizes.

    I was trying to explain the thing in terms the audience would understand...!

    Have you never noticed how the first VLF starts at offset 8192? (run DBCC LOGINFO)

    Why do you suppose that is? 😉

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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