locked
Monitor : Transaction Log Free Space (%) SQL 2016 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    The Transaction Log Free Space (%) is not working. The SQL 2016 Management Pack version is 7.0.7.0, the MP guide clearly says that -  "Fixed issue: the "Transaction Log Free Space (%)" monitor does not work"

    For one of our DB I changed the Threshold to 100% and Sample count to 1. The default Threshold is 10% and Sample Count 6.

    Another query is - How does this monitor work, Is it running as a script or capturing any performance counter ?

    Also, there are product knowledge says - there is different settings for Auto Growth and No Auto Growth options.

    Is there any alternative ways to monitor the transaction log files.

    Need a serious help for this issue.

    Amanpreet Singh Bansal

    Tuesday, February 26, 2019 2:34 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    the monitor uses a Performance Counter to collect the data:

    Microsoft.SQLServer.Windows.Monitor.Database.TransactionLogSpaceFreePercent (UnitMonitor)

    By default this monitor is not enabled. Did you enable (overwrite) it for your SQL servers?

    You mentioned "For one of our DB I changed the Threshold to 100% and Sample count to 1. The default Threshold is 10% and Sample Count 6." but this would not be correct if you want to produce alert, because it is a % Free Disks Space, so if you want to test you need to test with small numbers, like 1 or 2%. 100% Free Disks space would be the opposite - you have no problems with space. 

    About Autogrow: Yes, autogrow and the related settings affects of course the Free Space of the transaction log, so it should be also considered when configuring the monitor. 

    Hope I could clarify this. Regards,

    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Tuesday, February 26, 2019 9:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Stoyan

    I did see the alert after making the threshold to 100% as this is free space available and I wanted to alert if 100% logs are free.

    Transaction Log Free Space (%) of the database "XXXX" in SQL instance "XXXX" on computer "XXXX.XXX.XXX.XXX" is too low. See "alert context" tab for more details. Space available: 98.9357896741691%.

    The graph also shows when ever the log touches 100% free we are getting the alert.

    I have now changed the Th to 10% and Number of sample 10 to 1 only.

    I wanted to know if this monitor check the same results as checked by below query :

    DBCC SQLPERF(LOGSPACE);

    GO  


    Database Name Log Size (MB) Log Space Used (%) Status        
    ------------- ------------- ------------------ -----------   
    master         3.99219      14.3469            0   
    tempdb         1.99219      1.64216            0   
    model          1.0          12.7953            0   
    msdb           3.99219      17.0132            0   
    AdventureWorks 19.554688    17.748701          0  


    If not do we have any ways to check it, may be any community management pack or custom monitor.

    

    Amanpreet Singh Bansal



    • Edited by Aman Bansal Saturday, March 2, 2019 10:32 AM
    Saturday, March 2, 2019 10:30 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It's hard to say how the monitor gets the result because it relies on a custom dll , which we would have to decompile to see exactly what it's doing.
    Saturday, March 2, 2019 3:29 PM