Post reply

high VLF proactive monitoring

  • October 13, 2023 at 4:58 am

    #4307143

    Dear Experts,

    Need advise on how to get proactive alerts for high VLF counts that being when any db generates high vlf than threshold it should alert the DBA either via sql server databasemail or operator, currently i am having a mechanism where it generates a report on daily basis but the problem here it is scheduled not triggered , so when there is high count after the scheduled report it is difficult to find if there are high vlf, so basically looking for trigger based code or on the fly alerting mechanism without any additional cost for buying third party application. ( if free we can definitely consider 🙂  )

     

    Thanks

    MJ

  • October 13, 2023 at 5:24 am

    #4307149

    It's not like the VLF count will go down auto-magically and so your morning report should do just fine in reporting databases that have a high VLF count.  If the Log File Growth pattern for your databases is setup correctly, there shouldn't be an issue.

    If you're not the one that's setting up new databases, then your morning report should also include the "born on" date and you can check any new ones that appear on the morning report.

    To the best of my knowledge, there is no "triggering mechanism" that count's VLFs.  About the best you could do (IIRC) is setup an event for when the database grows and the have that fire a stored procedure to count VLFs and report if there are too many.

    Personally, I think the latter is NOT worth the effort.

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

  • October 13, 2023 at 10:33 am

    #4307329

    MJ-1115929 wrote:

    Dear Experts,

    ... so when there is high count after the scheduled report it is difficult to find if there are high vlf

    ...

    MJ

    why is it difficult? you just need to keep a daily audit of the number of VLF per database and you report when the delta is higher than your desired threshold .

    but before you start reporting on this ensure that all your db's have the correct growth settings for its usage (NEVER a percentage!!)

  • October 13, 2023 at 3:01 pm

    #4307482

    You don't need to actually count VLFs yourself, you can use sys.dm_db_log_stats instead (via column total_vlf_count).

    You could fairly easily capture previous count(s) and compare them to current count using a on whatever run schedule you wanted.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 13, 2023 at 7:38 pm

    #4307632

    Also be careful on setting a hard-coded single number for the threshold.  For some VLDB's you could have a transaction log that is hundred's of GB's.  If that transaction log were grown out at 1GB per growth - with 1 VLF per GB (not likely) you would have 100 VLF's per 100GB of log.

    At just 2 VLF's per 1GB (500MB VLF) you would have 200 VLF's for every 100GB's log.  A transaction log sized at 250GB would then have 500+ VLF's which is perfectly fine for that system.  It will still hit on the 'recommended' threshold and show up as having too many VLF's.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 15, 2023 at 9:45 pm

    #4308663

    I created a monitoring tool, DBA Dash that collects VLF counts for your SQL instances.  The GUI has a nice report that shows you the maximum VLF count for each of your SQL instances.  You can then drill down from this report to see the databases with the high VLF counts.  By default, DBA Dash collects VLF counts only once daily but the schedules are fully customizable.  The tool doesn't do any alerting on VLF count, but you could setup your own alert based on the data in the repository database.

    I probably wouldn't want to be alerted about VLF counts myself.  I think it's good to be proactive about them and include them as part of daily/weekly or monthly checks.  The DBA Dash report will make this a very quick process.  It will also help with your other daily checks and performance monitoring.  DBA Dash is free & open source.

    Attachments:
    You must be logged in to view attached files.

    DBA Dash - Free, open source monitoring for SQL Server

  • October 25, 2023 at 4:11 am

    #4314360

    Thanks all experts who provided their thoughts and knowledge and appreciate the same.

     

Viewing 7 posts - 1 through 6 (of 6 total)

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