Get SQL Server job schedule description with powershell

Publicado: 4 de julho de 2011 em Virtual PASS BR
Tags:, , ,

Olá pessoal,

Muitas vezes nós precisamos obter a descrição dos jobs e seus schedules, no entanto, sabemos que isso não é uma tarefa fácil via TSQL. Pensando nisso, eu e Italo ( DBA que trabalha comigo ), criamos um script em Powershell que exibe os dados de cada job do servidor. Podendo inclusive listar jobs de vários servidores.

Segue script abaixo:

#Mesmo que CLS
Clear-Host

#Remove as variáveis. Este comando é necessário porque as variáveis em Powershell não são declaradas.
#Variáveis em Powershell são criadas conforme necessário na primeira atribuição
Remove-Variable -Name * -Force -ErrorAction SilentlyContinue

#Carrega namespaces
#region Assembly
      [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
      [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
#endregion Assembly

#Pega a lista de servidores. Para listar jobs de mais de um servidor basta adicionar o nome dos mesmos
#no arquivo TXT abaixo
$sqlservers = Get-Content "c:\servers.txt"

#region Funcoes

#Função que recebe o valor de FrequencyInterval e realiza a operação binária para verificar a frequência dos
#dias de execução
function retornaDias {
param ($valor)

if ($valor -eq 62) 
{ 
    $ret = " nos dias úteis" 
}
else 
{
    if ($valor -eq 65) 
    { 
        $ret = " nos fins de semana" 
    } 
    else 
    {
        if ($valor -eq 127) 
        { 
            $ret = " todos os dias" 
        } 
        else 
        {
            if (($valor -band 1) -eq 1) 
            { 
                $ret = "Domingo, " 
            }
            if (($valor -band 2) -eq 2) 
            { 
                $ret += "Segunda-feira, " 
            }
            if (($valor -band 4) -eq 4) 
            { 
                $ret += "Terça-feira, " 
            }
            if (($valor -band 8 ) -eq 8 ) 
            { 
                $ret += "Quarta-feira, " 
            } 
            if (($valor -band 16) -eq 16) 
            { 
                $ret += "Quinta-feira, "
            }
            if (($valor -band 32) -eq 32)
            { 
                $ret += "Sexta-feira, " 
            }
            if (($valor -band 64) -eq 64) 
            { 
                $ret += "Sábado, " 
            }
            $ret = " no(s) dia(s) " + $ret.substring(0,$ret.Length - 2)
        }
    }
}
return $ret 
}

#Função que recebe o valor de FrequencySubDayTypes e realiza a operação binária para verificar a frequência dos
#momentos de execução
function comumFrequencia {
param ($param) 

switch ($param.FrequencySubDayTypes)
    {
        "Once" 
        { 
            $ret += " às " + $param.ActiveStartTimeOfDay
        } 
        "Minute"
        {
            $ret += " a cada " + $param.FrequencySubDayInterval + " minuto(s) entre " + $param.ActiveStartTimeOfDay + " e " + $param.ActiveEndTimeOfDay
        }
        "Hour"
        {
            $ret += " a cada " + $param.FrequencySubDayInterval + " hora(s) entre " + $param.ActiveStartTimeOfDay + " e " + $param.ActiveEndTimeOfDay
        }
        "Second"
        {
            $ret += " a cada " + $param.FrequencySubDayInterval + " segundo(s) entre " + $param.ActiveStartTimeOfDay + " e " + $param.ActiveEndTimeOfDay
        }                            
        default { $param.FrequencySubDayTypes }
    }
$ret += " [Inicia em: " + $param.ActiveStartDate.ToString("dd/MM/yyyy") +" - Termina em: " + $param.ActiveEndDate.ToString("dd/MM/yyyy") + "]"    

return $ret
}

#Função para retornar a descrição do estado do JOB/Schedule
function retornoAtividade{
param ($param)

    if ($param -eq "True") 
    { 
        $ret = " (Ativo)" 
    }
    else 
    { 
        $ret = " (Inativo)" 
    }

    return $ret
}

#endregion Funcoes

#region Principal

#Faz um laço em cada servidor contido no TXT
foreach($sqlserver in $sqlservers)
{    

    #Cria uma nova conexão para cada servidor do laço.    
    #region Connection

        $con = new-object Microsoft.SqlServer.Management.Common.ServerConnection
        $con.ServerInstance = $sqlserver
        $con.LoginSecure = $true
        $con.Connect()

    #endregion Connection

    #Cria um objeto Server
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server($con)

    #Armazena na variável $Jobs, todos os jobs do servidor atual
    $jobs = $srv.JobServer.Jobs

    #Variável auxiliar usada no laço
    $j = 0;

    #Imprime o nome do servidor
    "### Servidor: " + $sqlserver
    ""

    #Faz um laço em todos os jobs do servidor atual
    foreach($job in $jobs)
    {
        #Caso queira listar todos os jobs basta comentar este IF
        if ($job.Name -like "L*")
        {     
             ++$j;

            #Variável usada para listar a descrição
            $texto = $j.ToString() + ") Job: " + $job.Name;
            $texto += retornoAtividade($job.IsEnabled);
            $texto;
            $i = 0;

            #Percorre cada schedule do job
            foreach($sch in $job.JobSchedules)
            {
                $freq = ""
                ++$i;
                $text = $j.ToString() + "." + $i.ToString() + ") Schedule: " + $sch.Name; 
                $text += retornoAtividade($sch.IsEnabled);
                $text;

                #Verifica o tipo de frequência    
                switch ($sch.FrequencyTypes)
                {
                    "OnIdle" 
                    { 
                        $freq = "Inicia quando a CPU estiver ociosa"; 
                    }
                    "Daily" 
                    { 
                        $freq = "Diariamente, a cada "  + $sch.FrequencyInterval + " dia(s)";
                        $freq += comumFrequencia($sch);
                    }
                    "Monthly" 
                    { 
                        $freq = "Mensalmente, no dia " + $sch.FrequencyInterval + " a cada " + $sch.FrequencyRecurrenceFactor + " mes(es)";
                        $freq += comumFrequencia($sch);
                    }
                    "MonthlyRelative"
                    {
                        $freq = "Mensalmente, no(a) "
                        switch ($sch.FrequencyRelativeIntervals)
                        {
                            "First"     { $freq += "primeiro(a)" }
                            "Second" { $freq += "segundo(a)" }
                            "Third" { $freq += "terceiro(a)" }
                            "Fourth" { $freq += "quarto(a)" }
                            "Last" {  $freq += "último(a)" }
                            default { $sch.FrequencyRelativeIntervals } 

                        }

                        #Verifica o intervalo
                        switch ($sch.FrequencyInterval)
                        {
                            1 { $freq += " domingo" }
                            2 { $freq += " segunda-feira" }
                            3 { $freq += " terça-feira" }
                            4 { $freq += " quarta-feira" }
                            5 { $freq += " quinta-feira" }
                            6 { $freq += " sexta-feira" }
                            7 { $freq += " sábado" }
                            8 { $freq += " dia" }
                            9 { $freq += " dia útil" }
                            10 { $freq += " dia da semana" }
                            default { $sch.FrequencyInterval }
                        }

                        $freq += " a cada " + $sch.FrequencyRecurrenceFactor + " mes(es), "
                        $freq += comumFrequencia($sch);
                    }

                    "Weekly" 
                    { 
                        $freq = "Semanalmente, a cada " + $sch.FrequencyRecurrenceFactor + " semana(s)"; 
                        $freq += retornaDias($sch.FrequencyInterval); 
                        $freq += comumFrequencia($sch);                        
                    }

                    "AutoStart" 
                    { 
                        $freq = "Inicia automaticamente quando o SQL Server Agent inicia" 
                    }

                    "OneTime" 
                    { 
                        $freq = "Uma vez em " + $sch.ActiveStartDate.ToString("dd/MM/yyyy") + " às " + $sch.ActiveStartTimeOfDay 
                    }

                    default 
                    { 
                        $freq = $sch.FrequencyTypes 
                    }
                }
                    "Ocorrência: " + $freq
            }
        }
        ""        
    }
    "### " + $sqlserver + " - Total de jobs: " + $j.ToString(); 
}

#endregion Principal

Abraço,

Demétrio Silva
MCP | MCTS | MCITP | MCT Adm/Dev/BI
IBM Optim Certification

Anúncios
comentários
  1. Marcel Inowe disse:

    Demétrio e Italo meus amigos, vocês são feras, esse script é nota 1000. Parabéns.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s