PowerShell e o dia a dia do DBA – III


Continuação – III / Jobs/Backups – Checar a duração dos JOBS em todos os servidores de produção. Verificar se o tempo de execução é normal.

Todos os scripts eu criei um database chamado DBA no meu servidor central e um schema diario. As tabelas sermpre serão criadas neste schema e database.

Para todos os scripts, gerar um txt com os nomes dos servidores  envolvidos. Eu o chamei de servidores.txt e fica assim :

SERVER1\MSSQLSERVER_1
SERVER2\MSSQLSERVER_2
SERVER3\SQL2005

Os dois primeiros são 2008 e o terceiro 2005

Eu o gravo numa pasta chamada c:\dadosps, vocês irão ver esta chamada em todos os scripts

Uma outra coisa que eu não disse é que a gente pode segmentar o que é Job de Backup, o que é job de seilá o que..etc.. Como ?…. categorizando eles, criando uma categoria para cada um e colocando os jobs em sua devida categoria. Isso também é simples de pegar, tanto por TSQL como pela SMO. Aqui não está feito, mas fica-se a idéia que dá para ser feito (na verdade 99% dá para ser feito não é ?….basta a gente ter paciência , estudar  e fuçar muito)

Verificação Diaria

Jobs/Backups

Checar a duração dos JOBS em todos os servidores de produção. Verificar se o tempo de execução é normal.

 

Bom pessoal, continuando nossos scripts PS, este é um script que me deu trabalho. Eu queria montar usando a SMO mas meus connhecimentos com ela ainda estão se aprimorando (Fabiano, to precisando de ajuda heheheh). Desta maneira, montei usando selects nas tabelas de sistema sysjobs e sysjobhistory. É uma maneira de mostrar como retornar dados de select para o PS. Testei exaustivamente e está funcionando.

Este script ele varre as tabelas sysjobs e sysjobshistory dos servidores, pega o ultimo tempo de execução do job (em segundos – usaremos uma função para isso, ela tera que ser criada em cada servidor na msdb, mas é bem interessante deixá-la pois quando precisarmos fazer calculos de HHMMSS para segundos ela faz), pega a média em Segundos (tirando a ultima execução) e traz os que a ultima execução foi acima da média.

Mas agora entra a parte de conceito e de como você pretende trabalhar. Digamos que a ultima execução demorou muito mais tempo que o normal. Esta não entra na média, mas na proxima vez que ele rodar ela vai entrar ou seja, a média pode ser um valor que não condiz. Eu fiz desta maneira, mas no meu ponto de vista aqui seria interessante não calcularmos a média e sim ter uma tabelinha central de controle com a duração que cada job deve ter (inputado manualmente). Algo como "meu job a do servidor y tem que rodar em n segundos, meu job b do servidor y tem que rodar em b segundos..etc..etc)

Esta maneira que eu disse, usando a tabelinha é até mais fácil, mas vamos deixar calculando a média.

Primeira Passo criar a tabela que recebera os jobs

Use DBA

create table
diario.tb_JobsAcimaMedia(
Servidor varchar(50),
data datetime,
nomejob varchar(100),
LastRunDate varchar(10),
LastRunTime varchar(10),
RunDurationSec int,
MediaTempoSec int )

Segundo Passo criar a função que transoforma de HHMMSS para segundos em todos os MSDB de todos os sevidores

CREATE FUNCTION dbo.udf_JobRunTime2Sec
(@runtime int)
RETURNS int
AS
BEGIN
declare @runtimeinseconds int
declare @runtimeaschar char(7)
declare @hours int
declare @minutes int
declare @seconds int

SET @runtimeaschar = right(‘0000000’ + convert(varchar(7), @runtime), 7)

SET @hours = CAST(SUBSTRING(@runtimeaschar,1,3) AS INT)
SET @minutes = CAST(SUBSTRING(@runtimeaschar,4,2) AS INT)
SET @seconds = CAST(SUBSTRING(@runtimeaschar,6,2) AS INT)

SET @runtimeinseconds = @seconds + (@minutes * 60) + ((@hours * 60)*60)

RETURN (@runtimeinseconds)
END
GO

Pronto..Agora vem o Script em PS

$datas = (get-date).toshortdatestring()

##########################################################
#Atribui o Dabatase e Server central que receberão os dados
##########################################################

$ServidorCentral = "SERVER1\MSSQLSERVER_1"
$DatabaseCentral = "DBA"

foreach ($svr in get-content "C:\dadosps\servidores.txt" )
{

   $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=" + $svr + "; Initial Catalog=MSDB; Integrated Security=SSPI")  
   $conn.Open()
   $sql = "
SELECT   
        a.[Job_ID]
        ,a.[Name]
        ,LEFT(a.[Description],40) AS [Description]
        ,CONVERT(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(8),b.run_date)),110) AS [Last Run Date]
        ,SUBSTRING(RIGHT(‘00000’ + cast(b.run_time as VARCHAR(6)),6),1,2) + ‘:’ + SUBSTRING(RIGHT(‘00000’ + cast(b.run_time as VARCHAR(6)),6),3,2) + ‘:’ + RIGHT(‘0’ + cast(b.run_time as VARCHAR(6)),2) AS [Last Run Time]
        ,msdb.dbo.udf_JobRunTime2Sec(b.run_duration) RunDurationSEC
        –,SUBSTRING(RIGHT(‘00000’ + cast(b.run_duration as VARCHAR(6)),6),1,2) + ‘:’ + SUBSTRING(RIGHT(‘00000’ + cast(b.run_duration as VARCHAR(6)),6),3,2) + ‘:’ + RIGHT(‘0’ + cast(b.run_duration as VARCHAR(6)),2) AS [Last Run Dur] 
       ,(    SELECT     –STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), AVG(isnull(n.[run_duration],0))), 6), 5, 0, ‘:’), 3, 0, ‘:’) MediaTempo
                AVG(isnull(msdb.dbo.udf_JobRunTime2Sec(n.[run_duration]),0)) MediaTempo
            FROM   [msdb].[dbo].[sysjobhistory] AS n
            WHERE n.[job_id] = a.[job_id] and n.run_status = 1
            and convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(8),n.run_date)),110) + ‘ ‘ + SUBSTRING(RIGHT(‘00000’ + cast(n.run_time as VARCHAR(6)),6),1,2) + ‘:’ + SUBSTRING(RIGHT(‘00000’ + cast(n.run_time as VARCHAR(6)),6),3,2) + ‘:’ + RIGHT(‘0’ + cast(n.run_time as VARCHAR(6)),2) ,110) < convert(datetime,CONVERT(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(8),b.run_date)),110) + ‘ ‘ + SUBSTRING(RIGHT(‘00000’ + cast(b.run_time as VARCHAR(6)),6),1,2) + ‘:’ + SUBSTRING(RIGHT(‘00000’ + cast(b.run_time as VARCHAR(6)),6),3,2) + ‘:’ + RIGHT(‘0’ + cast(b.run_time as VARCHAR(6)),2) ,110)
        ) MediaTempo

into #Temp
FROM  [msdb].[dbo].[sysJobs] AS a
INNER JOIN [Msdb].[dbo].[sysJobHistory] AS b
ON a.[Job_ID] = b.[Job_ID]
WHERE              b.[Instance_ID] =    (    SELECT TOP 1 c.[Instance_ID]
                            FROM Msdb.dbo.sysJobHistory AS c
                            WHERE c.Job_ID = b.Job_ID
                            AND c.[Step_ID] = 0
                            ORDER BY c.[Run_Date] DESC, c.[Run_Time] DESC
                        )
AND b.[Step_ID] = 0
and b.run_status =1

select    *
from #temp
where not mediaTempo is null
and rundurationSEC > MediaTempo "

   $command= New-Object System.Data.SqlClient.sqlcommand($sql,$conn)
   $reader = $command.executereader()
   while ($reader.read())
   {
    $nomejob = $reader[1]
        $LastRunDate = $reader[3]
        $LastRunTime = $reader[4]
        $RunDurationSec =$reader[5]
        $MediaTempoSec = $reader[6]

       $sql1 = "set dateformat dmy insert into diario.tb_JobsAcimaMedia(Servidor,data,nomejob,LastRunDate,LastRunTime,RunDurationSec,MediaTempoSec) values (‘$svr’,’$datas’,’$nomejob’,’$LastRunDate’,’$LastRunTime’,’$RunDurationSec’,’$MediaTempoSec’)"
        Invoke-Sqlcmd -ServerInstance $ServidorCentral -Database $DatabaseCentral -Query $sql1
   }

$reader.close

$conn.close

}

O select é simples, não tem segredo, dá para modificar da maneira que for preciso, colocando um delay entre a ultima execução e a média. Aqui esta tudo que for acima, mas pode se colocar tudo que for 10% acima..etc…só mudar a codição final. O Null eu tiro pois indica que não teve execução antes ainda.

select    *
from #temp
where not mediaTempo is null
and rundurationSEC > MediaTempo

Da mesma maneira, gerar um .ps1 e colocar num job de execução powershell

 

I´ll be back

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s