none
DB File Allocated Space Unused Monitor

    Question

  • Hello, I wonder if there is some way to alert when unused space in database file is below some threshold. There is already rule like that but SQL MP only has monitor which checks space left in db file  and if Auto Grow is enabled then also space left on media/drive. 

    We have databases which have auto grow enabled but anyway we would like to have some info before auto grow will occur.


    • Edited by Furselek Monday, July 2, 2018 9:03 AM
    Monday, July 2, 2018 9:01 AM

All replies

  • As there is already performance rule collecting this information maybe it would be some way with powershell to get this performance data for this classinstance but how to add that monitor for that agent to get info from opsmgr server...
    Tuesday, July 3, 2018 1:55 PM
  • I made some workaround as I already have values in OpsMgrDW I am pulling them out on ManagementServer via Powershell and copy as text files to servers I want to get that monitor to work. Then I did set monitor which runs for each DB file and get data from that exported file with specific name i pull out. 

    It turned out that name or even a path for DB File is not unique therefore I tried to use "ID" as parameter so i guess SCOMMonitoringObject -id , or SCOMClassInstance -id. 

    Unfortunately it turned out that $Target/Id$ as parameter for SQL Server DB File target is not beeing recognized and I am not sure how can I use more than one parameter to maybe combine two argumets to have unique name.

    Powershell Monitor:

    param($Arguments) 
    
    #Load the MOMScript API and the PropertBag provider
    $API = New-Object -comObject "MOM.ScriptAPI"
    $bag = $api.CreatePropertyBag()
    
    #Main script
    
    $URL = Get-Content -path "c:\Program Files\Microsoft Monitoring Agent\Agent\Script\$Arguments.txt"
    $UNUSED = [double]$url
    
    #Add the data into the PropertyBag
    $bag.AddValue("FreeSpace",$UNUSED)
    
    #Log an event that our script is complete
    $api.LogScriptEvent("DBFileFreeSpace.ps1",3281,0,"Checked DB File Free Space %, which is $UNUSED, URL is $URL and Argument is $Argument")
    
    #Output the PropertyBag data for SCOM consumption:
    $bag  
     

    Parameters: 

    $Target/Id$

    Script which gets the performance values:

    ## SET SOME VALUES AS VARIABLES
    $COMPUTERNAME =@(
    
                    #'server1',
                    'server2'
                    
                    )
    
    $PERFCOUNTERTARGETCLASSNAME = 'SQL Server 2014 DB File'
    $PERFCOUNTERNAME = 'DB File Allocated Space Unused (%)'
    
    Foreach ($COMP in $COMPUTERNAME)
    {
    
    $name = Get-ChildItem -Path "\\$COMP\C$\Program Files\Microsoft Monitoring Agent\Agent\" | Where-Object {$_.Name -like "Script"}
    if (!$name)
    {New-Item -ItemType directory -Path "\\$COMP\C$\Program Files\Microsoft Monitoring Agent\Agent\Script"}
    
    ## RETRIEVE THE CLASS INSTANCE CONTAINING THE PERFORMANCE DATA
    $class = Get-SCOMClass -DisplayName $PERFCOUNTERTARGETCLASSNAME
    $ClassInstance = Get-SCOMClassInstance -Class $Class | Where-Object {$_.'[Microsoft.Windows.Computer].PrincipalName' -like $COMP}
    $i = 0
    Foreach ($CI in $ClassInstance)
    {
    ## RETRIEVE THE PERFORMANCE COUNTER DATA
    $DBFREESPACE = $CI.GETMONITORINGPERFORMANCEDATA() | ?{$_.COUNTERNAME -EQ $PERFCOUNTERNAME}
    
    ## RETRIEVE THE PERFORMANCE SAMPLES
    $SAMPLESTARTTIME = (GET-DATE).ADDHours(-6)
    $SAMPLEENDTIME = (GET-DATE)
    $PERFORMANCEVALUES = $DBFREESPACE.GETVALUES($SAMPLESTARTTIME,$SAMPLEENDTIME)
    
    ## VIEW THE MOST RECENT SAMPLE
    $UNUSED = $PERFORMANCEVALUES| SELECT-OBJECT -LAST 1
    $FREE = [Math]::Round($UNUSED.SampleValue, 2)
    $Path = $CI.path
    $ID = $CI.Id
    #$FREE | Out-File "\\$COMP\C$\Program Files\Microsoft Monitoring Agent\Agent\Script\$ID.txt" 
    $FREE | Out-File "\\$COMP\C$\Program Files\Microsoft Monitoring Agent\Agent\Script\$Path.txt" 
    }
    }
    

    Anyone has some idea how to make it work ?

    Wednesday, July 4, 2018 11:11 AM
  • I solved that. You can close it.
    Friday, July 13, 2018 1:32 PM