none
Powershell Consuming a lot of memory RRS feed

  • Question

  • Hi guys,

    i've been running a powershell script for a while but since a week ago the memory of the powershell just keep increasing, forcing me to stop de script and restart it. I think it is due a memory leak, but i dont know how to find it.

    Basically what the code does is a loop of 10 hours with the instruction bellow:

    1. Connect to a database

    2. Execute a query

    3. See if the result is equal to the last executing

    4. if it is not equal it pushes the data do powerbi

    5. Goes back to (1)

    #Setting up default Proxy Credentials
    (New-Object System.Net.WebClient).Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
    #Importing Oracle Connector to Powershell
    Add-Type -Path "C:\oracle\Ora121_32-Bit\ODP.NET\bin\4\Oracle.DataAccess.dll"
    
    #User and Password for the PowerBi Module for powershell
    $password = "Password" | ConvertTo-SecureString -asPlainText -Force
    $username = "User" 
    $credential = New-Object System.Management.Automation.PSCredential($username, $password)
    
    #Connecting to PowerBi Service
    Connect-PowerBIServiceAccount -Credential $credential
    
    #Write-Log -Text "Deleting existing data."
    
    #Deleting all the data on the tables of each dataset
    $Delete1 = "PowerBi api url"
    $Delete2 = "PowerBi api url"
    $Delete3 = "PowerBi api url"
    #Dataset Compras Online
    Invoke-PowerBIRestMethod -Url $Delete1 -Method Delete
    
    #Dataset Compras Online 2
    Invoke-PowerBIRestMethod -Url $Delete2 -Method Delete
    
    #Dataset Compras Online 3
    Invoke-PowerBIRestMethod -Url  $Delete3 -Method Delete
    
    #Loop Variables
    $TimeStart = Get-Date
    $TimeEnd = $TimeStart.AddHours(14) #how long will the powershell run? (14 hours)
    $TimeClean = $TimeStart.AddMinutes(1)
    
    $Start = 1 #Variable to make the first push to the dataset
    
    #Database connection info
    $datasource = 'DB';
    $username = "DB.USER"
    $password = "DB.PASSWORD"
    $connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource #Connection string for oracle
    $query_location = "Location of the query" #Defining where we stored the query as a txt file
    $query = Get-Content -Path $query_location -Raw #importing the query
    
    ##you would find your own endpoint in the Power BI service
    $endpoint = "api url to send data"
    $endpoint2 = "api url to send data"
    $endpoint3 = "api url to send data"
    
    #Loading the Count variable to make the versioning controll
    $Location = 'Count location'
    $COUNT = import-clixml -path $Location
    $COUNT = $COUNT + 1
    $connection = New-Object Oracle.DataAccess.Client.OracleConnection($connectionString) #Create the oracle object to make the connection
    $connection.open(); #Open the connection
    $command = $connection.CreateCommand(); #Create a command for oracle
    $command.CommandText = $query; #Store the query on the command
    
    #Fetch data and write out to files
    Do {
    
        $TimeNow = Get-Date
        $reader = $command.ExecuteReader() #Execute the query
    
        #Store each line in a Hash Table array
        $objects = @(
            while ($reader.Read()) {
    
                [pscustomobject]@{
                    "REG_COMERCIAL"               = $reader['REG_COMERCIAL'];
                    "DESTINACAO"                  = $reader['DESTINACAO'];
                    "QTD_CBOT"                    = $reader['QTD_CBOT'];
                    "QTD_PREMIO"                  = $reader['QTD_PREMIO'];
                    "QTD_CUSTO"                   = $reader['QTD_CUSTO'];
                    "QTD_CAMBIO"                  = $reader['QTD_CAMBIO'];
                    "QTD_FIXACAO"                 = $reader['QTD_FIXACAO'];
                    "QTD_FIXO"                    = $reader['QTD_FIXO'];
                    "BASE_PRC_FIXO"               = $reader['BASE_PRC_FIXO'];
                    "BASE_CBOT"                   = $reader['BASE_CBOT'];
                    "QTD_CBOT_COMP"               = $reader['QTD_CBOT_COMP'];
                    "QTD_PREMIO_COMP"             = $reader['QTD_PREMIO_COMP'];
                    "QTD_CUSTO_COMP"              = $reader['QTD_CUSTO_COMP'];
                    "SPREDDOMC"                   = $reader['SPREDDOMC'];
                    "BASE_PREMIO"                 = $reader['BASE_PREMIO'];
                    "BASE_SPREAD_INTERIOR"        = $reader['BASE_SPREAD_INTERIOR'];
                    "BASE_MARG_FIX"               = $reader['BASE_MARG_FIX'];
                    "PREMIO_FOB_EQUIV_PAGO_FINAL" = $reader['PREMIO_FOB_EQUIV_PAGO_FINAL'];
                    "FOREX_RS_FINAL"              = $reader['FOREX_RS_FINAL'];
                    "FOREX_USS_FINAL"             = $reader['FOREX_USS_FINAL'];
                    "SHORT_FREIGHT_RS"            = $reader['SHORT_FREIGHT_RS'];
                    "SHORT_FREIGHT_USS"           = $reader['SHORT_FREIGHT_USS'];
                    "DESTINATION_TYPE"            = $reader['DESTINATION_TYPE'];
                    "DATA_ENTREGA"                = $reader['DATA_ENTREGA'];
                    "ANO_DATA_ENTREGA"            = $reader['ANO_DATA_ENTREGA'];
                    "PRECIFICACAO"                = $reader['PRECIFICACAO'];
                    "ITEM"                        = $reader['ITEM'];
                    "HORA"                        = $reader['HORA'].AddHours(-3);
                    "FATOR_DOLAR"                 = $reader['FATOR_DOLAR'];
                    "SFDDJ"                       = $reader['SFDDJ'];
                    "BASE_MARGEM_CUSTO"           = $reader['BASE_MARGEM_CUSTO'];
                    "DATA_DA_OPERACAO"            = $reader['DATA_DA_OPERACAO'];
                    "ID_NUMBER"                   = $COUNT
                    "PRODUTO"                     = $reader['PRODUTO'];
                    "NR_SLIP"                     = $reader['NR_SLIP_HORA'];
                    "CLASS_COMERCIAL"             = $reader['CLASS_COMERCIAL'];
                    "PTAX80"                      = $reader['PTAX80_FINAL'];
                    "TP_NEGOCIACAO"               = $reader['TP_NEGOCIACAO'];
                    "TP_OPERACAO"                 = $reader['TP_OPERACAO'];
                    "TIPOOPERACAO_ORIGEM"         = $reader['tipooperacao_origem'];
                    "TIPO_OPERACAO"               = $reader['TIPO_OPERACAO'];
                    "FOREX_EXPOSICAO"             = $reader['FOREX_EXPOSICAO'];
                    "FILIAL_COMPRA"               = $reader['DESCRICAO_FL_COMPRA'];
                    "STATUS"                      = $reader['STATUS'];
                    "VOLUME"                      = $reader['VOLUME'];
                    "TP_OPERACAO_CANC_REV"        = $reader['TP_OPERACAO_CANC_REV'];
                    "VALOR_UNIT_USS"              = $reader['VALOR_UNIT_US$'];
                    "BASE_PRC_FIXO_RS"            = $reader['BASE_PRC_FIXO_RS'];
                    "DESCRICAO_FL_ENTREGA"        = $reader['DESCRICAO_FL_ENTREGA'];
                    "EXPOSURE_USS"                = $reader['EXPOSURE_USS'];
                    "VL_LIQ_ADTO_USS"             = $reader['VL_LIQ_ADTO_USS'];
                    "FOREX_USS_Q"                 = $reader['FOREX_USS_Q'];
                    "SAFRA"                       = $reader['SAFRA'];
                    "CONTRATO"                    = $reader['CONTRATO'];
                    "DESCRICAO"                   = $reader['DESCRICAO'];
                    "PMK_PREMIO"                  = $reader['PMK_FOB_EQUIV_PAGO_FINAL'];
                    "PNL_PTAX_REAIS"              = $reader['PNL_PTAX_FEE_REAIS'];
                    "TX_CAMBIO_PGTO_DOLAR"        = $reader['TX_CAMBIO_PGTO_DOLAR'];
                    "PNL_CAMBIO_FF_REAIS"         = $reader['PNL_CAMBIO_FF_REAIS'];
                    "CTS_CAMBIO"                  = $reader['CTS_CAMBIO'];
                    "TX_CAMBIO_UTILIZADA_DOLAR"   = $reader['TX_CAMBIO_UTILIZADA_DOLAR'];
                    "ADDRESS"                     = $reader['ADDRESS'];
                    "HORA_SLIP"                   = $reader['HORA_SLIP'].AddHours(-3);
                }    
            }
        )
    
        #If the powershell just started, do the initial push
        if ($Start -eq 1) {
    
            $objects_old = $objects.PsObject.Copy()
            $Start = 0
            Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
            Invoke-RestMethod -Method Post -Uri "$endpoint2" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
            Invoke-RestMethod -Method Post -Uri "$endpoint3" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
    
    
        }
        else {
            #Get the query result ($objects) and compare with the last query result ($objects_old) based on the columns listed
            $o1 = $objects | Select "DESCRICAO","QTD_CBOT", "QTD_CUSTO", "QTD_CAMBIO", "QTD_FIXACAO", "QTD_FIXO", "BASE_PRC_FIXO", "BASE_CBOT", "QTD_CBOT_COMP", "SPREDDOMC", "BASE_PREMIO", "BASE_SPREAD_INTERIOR", "BASE_MARG_FIX", "PREMIO_FOB_EQUIV_PAGO_FINAL", "FOREX_RS_FINAL", "FOREX_USS_FINAL", "SHORT_FREIGHT_RS", "SHORT_FREIGHT_USS", "DESTINATION_TYPE", "DATA_ENTREGA", "ANO_DATA_ENTREGA", "PRECIFICACAO", "ITEM", "HORA", "FATOR_DOLAR", "SFDDJ", "BASE_MARGEM_CUSTO", "DATA_DA_OPERACAO", "PRODUTO", "NR_SLIP", "CLASS_COMERCIAL", "PTAX80", "TP_NEGOCIACAO", "TP_OPERACAO", "TIPOOPERACAO_ORIGEM", "TIPO_OPERACAO", "TT", "FILIAL_COMPRA", "STATUS","PNL_CAMBIO_FF_REAIS","CTS_CAMBIO","PNL_PTAX_REAIS", "FOREX_USS_Q"
            $o2 = $objects_old | Select "DESCRICAO","QTD_CBOT", "QTD_CUSTO", "QTD_CAMBIO", "QTD_FIXACAO", "QTD_FIXO", "BASE_PRC_FIXO", "BASE_CBOT", "QTD_CBOT_COMP", "SPREDDOMC", "BASE_PREMIO", "BASE_SPREAD_INTERIOR", "BASE_MARG_FIX", "PREMIO_FOB_EQUIV_PAGO_FINAL", "FOREX_RS_FINAL", "FOREX_USS_FINAL", "SHORT_FREIGHT_RS", "SHORT_FREIGHT_USS", "DESTINATION_TYPE", "DATA_ENTREGA", "ANO_DATA_ENTREGA", "PRECIFICACAO", "ITEM", "HORA", "FATOR_DOLAR", "SFDDJ", "BASE_MARGEM_CUSTO", "DATA_DA_OPERACAO", "PRODUTO", "NR_SLIP", "CLASS_COMERCIAL", "PTAX80", "TP_NEGOCIACAO", "TP_OPERACAO", "TIPOOPERACAO_ORIGEM", "TIPO_OPERACAO", "TT", "FILIAL_COMPRA", "STATUS","PNL_CAMBIO_FF_REAIS","CTS_CAMBIO" ,"PNL_PTAX_REAIS", "FOREX_USS_Q"
    
            #Convert by json and split lines
            $source = ($o1 | ConvertTo-Json) -split "`n"
            $target = ($o2 | ConvertTo-Json) -split "`n"
    
            #if the objects are different, push the data...
            if (diff $source $target) {
                if ($TimeNow -gt $TimeClean){
    
                    Invoke-PowerBIRestMethod -Url $Delete1 -Method Delete
                    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8" 
                    Invoke-PowerBIRestMethod -Url $Delete2 -Method Delete
                    Invoke-RestMethod -Method Post -Uri "$endpoint2" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
                    Invoke-PowerBIRestMethod -Url $Delete3 -Method Delete
                    Invoke-RestMethod -Method Post -Uri "$endpoint3" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
                    $TimeClean = $TimeNow.AddMinutes(8)
    
                } else {
    
                    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8" 
                    Invoke-RestMethod -Method Post -Uri "$endpoint2" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
                    Invoke-RestMethod -Method Post -Uri "$endpoint3" -Body (ConvertTo-Json @($objects)) -ContentType "application/json; charset=utf-8"
                    
                } 
                
                $objects_old = $objects.PsObject.Copy()
                $objects.Clear(
                $COUNT = $COUNT + 1
                
            }
        }
    
    } Until ($TimeNow -ge $TimeEnd) #Verify if its time to end the script
    
    $COUNT = 1
    $COUNT | export-clixml -path $Location
    $connection.Close(); #Close the connection
    $connection.Dispose();
    Invoke-PowerBIRestMethod -Url $Delete1 -Method Delete
    
    #Dataset Compras Online 2
    Invoke-PowerBIRestMethod -Url $Delete2 -Method Delete
    
    #Dataset Compras Online 3
    Invoke-PowerBIRestMethod -Url $Delete3 -Method Delete
    
    Disconnect-PowerBIServiceAccount
    


    • Edited by mThsDiniz Thursday, November 7, 2019 2:27 PM
    Thursday, November 7, 2019 2:25 PM

Answers

  • You never close and dispose the reader so it will always remain in memory.  Database readers must be explicitly closed and disposed,


    \_(ツ)_/

    • Marked as answer by mThsDiniz Thursday, November 7, 2019 2:55 PM
    Thursday, November 7, 2019 2:39 PM

All replies

  • You never close and dispose the reader so it will always remain in memory.  Database readers must be explicitly closed and disposed,


    \_(ツ)_/

    • Marked as answer by mThsDiniz Thursday, November 7, 2019 2:55 PM
    Thursday, November 7, 2019 2:39 PM
  • Perfect!! 

    Thank you so much!!

    Thursday, November 7, 2019 2:55 PM