Articles Posted in the " Log " Category

  • What Is Consuming My Log Space

    What Is Consuming My Log Space

    Often times DBA’s (including myself at the beginning of my DBA career) struggle to find out what is causing the log files to grow. Or what is consuming all the space in my log files. I understand there are many factors that can impact the log file size beside all the active transactions.

    Such as:

    • Recovery Model
    • If replication/logshipping/mirroring is set up
    • Frequency of transaction log back up
    • Longest running transaction
    • and many more

    I wanted to see a list of all the sessions that are consuming more than 1kb off log space for all the databases in the server.…


  • Fix High or Low VLF Count

    Fix High or Low VLF Count

    We started collecting a count of all VLF across the enterprise and yes there were few with high counts. I know there are no SET guidelines how many is too low or too high. After chatting with MVP Allen Kinsel (Blog|Twitter) and Kevin Conan (Twitter) I decided to fix using this threshold. You will be able adjust these threshold in my script to whatever you deem reasonable.

    WHERE
    ( (vlfcount > 50
    AND tLogSizeMB <= 8 * 1024)
    OR
    (vlfcount > 200
    AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024)