Banco de Dados

4 mai, 2010

Criando um checkList automático do banco de dados

Publicidade

Olá,
pessoal.

Assim que assumi a posição de DBA, encontrei muitos
artigos dizendo que todos os dias deveriam ser realizadas uma serie de
verificações, o famoso CheckList do DBA. Entre elas estavam verificar se os
Jobs rodaram com sucesso, se os backups foram realizados, se há espaço
disponível em disco etc.

No meu ambiente, cuido de cinco servidores com SQL
Server e realizar essa tarefa em cada um desses servidores me toma um
tempo razoável. Imagine, então, em ambientes maiores (como existem aos
montes por aí). Assim, encontrei na internet querys que me retornavam
algumas dessas informações, mas chegar todos os dias no trabalho e ter
que abrir o Management Studio para rodar várias querys ainda não era a
solução ideal.

Com isso, resolvi criar uma planilha Excell com
várias abas, que agrupa todas as informações que eu verificava
manualmente em um único local, com um tipo de informação por aba da
planilha. Esse relatório é enviado diariamente para o meu e-mail às
08:00 da manhã. Logo, quando chego, só tenho o trabalho de abrir a
planilha, analisar o CheckList e tomar as devidas medidas. Além disso, posso até acompanhar como está o meu banco de dados de casa ou do celular, para o caso de não ter ido ao trabalho por algum motivo.

01. Espaço
em Disco

Nessa aba teremos informações sobre como estão os drives
dos discos do nosso servidor SQL Server. Ela retornará o Drive, o
tamanho em MB, o tamanho que está sendo utilizado, o espaço livre, o
percentual de espaço que está sendo utilizado, o percentual disponível, e
o espaço desse disco que é utilizado por arquivos do SQL Server:

Drive Tamanho (MB) Utilizado(MB) Livre (MB) Utilizado (%) Livre
(%)
Ocupado SQL (MB)

02. Arquivos SQL

Nessa aba teremos informações
sobre todos os arquivos do SQL Server (.MDF, .LDF e .NDF). Com isso,
podemos verificar os caminhos desses arquivos, o tamanho atual, o
tamanho máximo que esse arquivo pode alcançar, o quanto esse arquivo
cresce, próximo tamanho que o arquivo terá e sua situação.

Database File Name Tamanho(MB) Tamanho Max(MB) Crescimento Próximo
Tamanho
Situação

03. Utilização LOG

Nessa aba teremos a informação
do percentual utilizado de todos os arquivos de log existentes no banco:

Database Log Size (MB) Space Used(%)

04. Backup

Uma das abas mais importantes. Nela,
saberemos o nome das databases que fizeram o backup com sucesso, o
horário de início, a duração, o recovery model de cada Database e o
tamanho do backup.

Database Nome Inicio Tempo Recovery Tamanho (MB)

05. Jobs Rodando

Muitas vezes me deparei
com a situação de chegar ao meu ambiente e encontrar vários Jobs rodando
(agarrados). Algumas vezes isso só era percebido quase no fim do dia.
Para que isso não aconteça mais, essa aba retornará todos os Jobs que
estão executando no momento da geração da planilha, no meu caso às 8:00
AM . Com essa informação, o problema pode ser resolvido rapidamente.

Job Data
Inicio
Tempo Execução

06. Jobs Failed

Quantas vezes você já
identificou um Job que falhou mas estava sem notificação? Isso acontece
muito em ambientes onde várias pessoas criam e alteram Jobs. Essa aba
identificará via query todos os Jobs que falharam mesmo quando o mesmo
não enviar nenhuma notificação. Será informado o nome do Job, o status, a
data e o tempo de execução e a mensagem retornada pelo Job.

Job Status Data Execução Tempo
Execução
SQL Message

Apresentadas as abas, vamos ao que interessa. Os scripts
abaixo preencherão a planilha com as informações do CheckList. Para
baixar essa planilha, clique aqui.

O caminho da planilha
utilizado nos scripts é “C:\FabricioLima\CheckList\CheckList do Banco de
Dados.xls”. Esse caminho deve ser alterado para onde a planilha for
salva.

Aba 1: monitoramento do espaço
em disco

Nessa aba criaremos uma procedure que
montará uma tabela com todas as informações sobre os drives disponíveis
no servidor.

Para as versões do SQL Server 2005 e 2008, caso a
opção Ole Automation Procedures não esteja habilitada em seu
servidor, a mesma deve ser habilitada.

sp_configure 'show advanced options',1
GO
reconfigure
GO
sp_configure 'Ole Automation Procedures',1
GO
reconfigure
GO
sp_configure 'show advanced options',0
GO
reconfigure

Após habilitada, devemos
criar a procedure abaixo em uma determinada database. Segue o script da
procedure:

CREATE PROCEDURE [dbo].[stpVerifica_Espaco_Disco]
AS
BEGIN

SET NOCOUNT ON

CREATE TABLE #dbspace (name sysname, caminho varchar(200),tamanho varchar(10), drive Varchar(30))

CREATE TABLE [#espacodisco] ( Drive varchar (10) ,[Tamanho (MB)] Int, [Usado (MB)] Int,
[Livre (MB)] Int, [Livre (%)] int, [Usado (%)] int, [Ocupado SQL (MB)] Int,[Data] smalldatetime)

Exec SP_MSForEachDB ‘Use ? Insert into #dbspace Select Convert(Varchar(25),DB_Name())”Database”,Convert(Varchar(60),FileName),Convert(Varchar(8),Size/128)”Size in MB”,Convert(Varchar(30),Name) from SysFiles’

DECLARE @hr int,@fso int,@mbtotal int,@TotalSpace int,@MBFree int,@Percentage int,
@SQLDriveSize int,@size float, @drive Varchar(1),@fso_Method varchar(255)

SET @mbTotal = 0

EXEC @hr = master.dbo.sp_OACreate ‘Scripting.FilesystemObject’, @fso OUTPUT

CREATE TABLE #space (drive char(1), mbfree int)
INSERT INTO #space EXEC master.dbo.xp_fixeddrives
Declare CheckDrives Cursor For Select drive,MBfree From #space
Open CheckDrives
Fetch Next from CheckDrives into @Drive,@MBFree
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @fso_Method = ‘Drives(“‘ + @drive + ‘:”).TotalSize’
SELECT @SQLDriveSize=sum(Convert(Int,tamanho))
from #dbspace where Substring(caminho,1,1)=@drive
EXEC @hr = sp_OAMethod @fso, @fso_method, @size OUTPUT
SET @mbtotal = @size / (1024 * 1024)
INSERT INTO #espacodisco
VALUES(@Drive+’:’,@MBTotal,@MBTotal-@MBFree,@MBFree,(100 * round(@MBFree,2) / round(@MBTotal,2)),
(100 – 100 * round(@MBFree,2) / round(@MBTotal,2)),@SQLDriveSize, getdate())

FETCH NEXT FROM CheckDrives INTO @drive,@mbFree
END
CLOSE CheckDrives
DEALLOCATE CheckDrives

IF (OBJECT_ID(‘_CheckList_Espacodisco ‘) IS NOT NULL) DROP TABLE _CheckList_Espacodisco

SELECT Drive, [Tamanho (MB)],[Usado (MB)] , [Livre (MB)] , [Livre (%)],[Usado (%)] ,
ISNULL ([Ocupado SQL (MB)],0) AS [Ocupado SQL (MB)]
into dbo._CheckList_Espacodisco
FROM #espacodisco

DROP TABLE #dbspace
DROP TABLE #space
DROP TABLE #espacodisco

END

Agora basta rodar a SP para geramos as informações na tabela
_CheckList_Espacodisco:

exec dbo.stpVerifica_Espaco_Disco  

Para
enviar os dados para a planilha, basta executar a query abaixo alterando
o caminho da mesma:

-- ABA ESPAÇO DISCO   
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
'SELECT Drive,    [Tamanho(MB)],[Utilizado(MB)],[Livre(MB)],[Utilizado(%)],[Livre(%)],[Ocupado SQL(MB)]  FROM [Espaço Disco$]')
SELECT Drive,[Tamanho (MB)],[Usado (MB)],[Livre (MB)],[Usado (%)],[Livre (%)],[Ocupado SQL (MB)]
from _CheckList_Espacodisco 

Segue um exemplo de como essa informação será retornada pela
planilha.

Drive Tamanho
(MB)
Utilizado(MB) Livre (MB) Utilizado (%) Livre (%) Ocupado SQL (MB)
C: 29989 21774 8215 73 27 0
E: 30718 25758 4960 84 16 490
F: 78520 68187 10333 87 13 0

Aba 2: monitoramento dos arquivos SQL

Para a geração dos dados dessa aba, basta executar o script
abaixo:

IF (OBJECT_ID('_CheckList_Arquivos_SQL') IS NOT NULL)  drop table _CheckList_Arquivos_SQL

create table dbo._CheckList_Arquivos_SQL (
[Name] varchar(250) , [FileName] varchar(250) , [Size] bigint, [MaxSize] bigint, Growth varchar(100), Proximo_Tamanho bigint, Situacao varchar(15))

insert into dbo._CheckList_Arquivos_SQL
select convert(varchar, name) as NAME ,Filename ,
cast(Size * 8 as bigint) / 1024.00 Size,
case when MaxSize = -1 then -1 else cast(MaxSize as bigint)* 8 / 1024.00 end MaxSize,
case when substring(cast(Status as varchar),1,2) = 10 then cast(Growth as varchar) + ‘ %’
else cast (cast((Growth * 8 )/1024.00 as numeric(15,2)) as varchar) + ‘ MB’end Growth,
case when substring(cast(Status as varchar),1,2) = 10
then (cast(Size as bigint) * 8 / 1024.00) * ((Growth/100.00) + 1)
else (cast(Size as bigint) * 8 / 1024.00) + cast((Growth * 8 )/1024.00 as numeric(15,2))
end Proximo_Tamanho ,
case when MaxSize = -1 then ‘OK’ — OK
when
( case when substring(cast(Status as varchar),1,2) = 10
then (cast(Size as bigint)* 8 / 1024.00) * ((Growth/100.00) + 1)
else (cast(Size as bigint) * 8/ 1024.00) + cast((Growth * 8 )/1024.00 as numeric(15,2))
end
) < (cast(MaxSize as bigint) * 8/1024.00) then ‘OK’ else ‘PROBLEMA’
end Situacao
from master..sysaltfiles with(nolock)
order by Situacao, Size desc

Gerada a
informação, a query abaixo deve ser utilizada para enviar os dados para a
planilha.

-- ABA ARQUIVOS SQL
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
'SELECT [DataBase],[File Name],[Tamanho(MB)],[Tamanho Max(MB)],[Crescimento],[Próximo Tamanho], [Situacao]   FROM [Arquivos SQL$]')
select Name,FileName,Size,MaxSize,Growth,Proximo_Tamanho,Situacao
from dbo._CheckList_Arquivos_SQL
order by Situacao desc, Size desc 

Quando o valor da coluna Tamanho Max(MB) dessa aba da
planilha for igual a -1, significa que esse arquivo não possui uma
restrição de crescimento.

Quando a coluna “Situacao” retornar o
valor PROBLEMA, significa que o arquivo não conseguirá crescer mais uma
vez, logo, esse arquivo de ver diminuído ou ter seu tamanho máximo
aumentado para que quando ele precise crescer o SQL Server não gere um
erro.

Aba 3: utilização do arquivo de log

Para a geração dos dados dessa aba, deve ser
criada a procedure abaixo que retornará as informações sobre os arquivos
de log.

CREATE procedure [dbo].[StpVerifica_Utilizacao_Log]
as
    DBCC SQLPERF (LOGSPACE) --ñ é possível inserir em uma tabela direto desse comando 

Agora que a procedure já existe, o código abaixo
deve ser executado para gerar a tabela com as informações para a
planilha.

IF (OBJECT_ID('_CheckList_Utilizacao_Log') IS NOT NULL)  DROP TABLE _CheckList_Utilizacao_Log

create table dbo._CheckList_Utilizacao_Log (
Nm_Database varchar(50),
Log_Size numeric(15,2),
[Log_Space_Used(%)] numeric(15,2),
status_log int)

insert dbo._CheckList_Utilizacao_Log
exec dbo.StpVerifica_Utilizacao_Log

Com os dados gerados, basta
mandar as informações para a planilha.

-- ABA LOG
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
'SELECT [Database],    [Log Size(MB)],[Space Used(%)] FROM [Log$]')
select Nm_Database, Log_Size,[Log_Space_Used(%)]
from dbo._CheckList_Utilizacao_Log
order by 3 desc 

Aba 4: backup

Com o
script abaixo, teremos a informação de todos os backups que foram
rodados desde o dia anterior às 18h. O intervalo de backup que será
retornado pode ser alterado para ficar de acordo com a realidade do seu
ambiente.

Na versão do SQL Server 2000, a coluna recovery_model
retornada na query abaixo não existe na tabela backupset, logo a
query deve ser alterada para retornar um espaço em branco nessa coluna.

IF (OBJECT_ID('_CheckList_Backup') IS NOT NULL)  DROP TABLE _CheckList_Backup

create table dbo._CheckList_Backup(database_name nvarchar(256),name nvarchar(256),
backup_start_date datetime,tempo int, server_name nvarchar(256), recovery_model nvarchar(120),
tamanho numeric(15,2))

DECLARE @Dt_Referencia datetime
SELECT @Dt_Referencia = cast(floor(cast(GETDATE() as float)) as datetime) — Hora zerada

insert dbo._CheckList_Backup
SELECT database_name, name,Backup_start_date, datediff(mi,Backup_start_date,Backup_finish_date) [tempo (min)],
server_name,recovery_model, cast(backup_size/1024/1024 as numeric(15,2)) [Tamanho (MB)]
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily BF ON B.media_set_id = BF.media_set_id
where Backup_start_date >= dateadd(hh, 18 ,@Dt_Referencia – 1 ) –backups realizados a partir das 18h de ontem
and Backup_start_date < dateadd (day, 1, @Dt_Referencia)
and type = ‘D’

Após
populada a tabela, a query abaixo deve ser utilizada para retornar os
dados para a planilha.

-- ABA BACKUP
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
'SELECT Database,    Nome,    Start,    [Tempo(Min)],    Recovery,    [Tamanho(MB)] FROM [Backup$]')
select database_name,name,backup_start_date,tempo,recovery_model, Tamanho
from dbo._CheckList_Backup
order by backup_start_date 

Aba 5: Jobs executando

Para verificarmos quais os Jobs que estão rodando, basta executar
o script abaixo. Essa query retorna o nome do Job, o horário de início e
o tempo em minutos que esse Job está executando.

Para fazer um
teste rápido, crie e execute um Job com a query waitfor delay
’00:05:00′. Em seguida, rode a query abaixo para retornar esse
Job que está sendo executado.

A informação dessa aba não está
disponível para a versão do SQL Server 2000, apenas para as versões 2005
e 2008.

IF (OBJECT_ID('_CheckList_JobsRodando') IS NOT NULL)  DROP TABLE _CheckList_JobsRodando

create table dbo._CheckList_JobsRodando(
Name varchar(256),
Data_Inicio datetime,
Tempo_Rodando int
)
insert into dbo._CheckList_JobsRodando
select name, run_Requested_Date, datediff(mi,run_Requested_Date,getdate())
from msdb..sysjobactivity A
join msdb..sysjobs B on A.job_id = B.job_id
where start_Execution_Date is not null and stop_execution_date is null

Para enviar as informações para a planilha:

-- ABA JOBS EM EXECUÇÃO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
'SELECT Job,[Data Inicio],[Tempo Execução]  FROM [Jobs em Execução$]')
select Name, Data_Inicio, Tempo_Rodando
from dbo._CheckList_JobsRodando    

Aba 6: lobs que falharam

Geralmente
recebemos e-mails e torpedos com os Jobs que falham, entretanto é muito
importante identificarmos todos os Jobs que falharam por outro meio,
pois esse Job pode estar sem notificação ou pode ocorrer algum problema
com seu servidor de e-mail. A query abaixo retornará todos os Jobs que
falharam desde as 17:00 do dia anterior, sendo que esse horário deve ser
configurado de acordo com a necessidade de cada um.

if OBJECT_ID('Tempdb..#Result_History_Jobs') is not null    drop table #Result_History_Jobs

create table #Result_History_Jobs(
Cod int identity(1,1),Instance_Id int, Job_Id varchar(255),Job_Name varchar(255),Step_Id int,Step_Name varchar(255),
Sql_Message_Id int,Sql_Severity int,SQl_Message varchar(3990),Run_Status int, Run_Date varchar(20),
Run_Time varchar(20),Run_Duration int,Operator_Emailed varchar(100),Operator_NetSent varchar(100),
Operator_Paged varchar(100),Retries_Attempted int, Nm_Server varchar(100))

IF (OBJECT_ID(‘_CheckList_Jobs_Failed’) IS NOT NULL) DROP TABLE _CheckList_Jobs_Failed

declare @hoje varchar (8)
declare @ontem varchar (8)
set @ontem = convert (varchar(8),(dateadd (day, -1, getdate())),112)

insert into #Result_History_Jobs
exec Msdb.dbo.SP_HELP_JOBHISTORY @mode = ‘FULL’ , @start_run_date = @ontem

select Job_Name, case when Run_Status = 0 then ‘Failed’
when Run_Status = 1 then ‘Succeeded’
when Run_Status = 2 then ‘Retry (step only)’
when Run_Status = 3 then ‘Canceled’
when Run_Status = 4 then ‘In-progress message’
when Run_Status = 5 then ‘Unknown’ end Status,
cast(Run_Date + ‘ ‘ +
right(’00’ + substring(Run_time,(len(Run_time)-5),2) ,2)+ ‘:’ +
right(’00’ + substring(Run_time,(len(Run_time)-3),2) ,2)+ ‘:’ +
right(’00’ + substring(Run_time,(len(Run_time)-1),2) ,2) as varchar) Dt_Execucao,
right(’00’ + substring(cast(Run_Duration as varchar),(len(Run_Duration)-5),2) ,2)+ ‘:’ +
right(’00’ + substring(cast(Run_Duration as varchar),(len(Run_Duration)-3),2) ,2)+ ‘:’ +
right(’00’ + substring(cast(Run_Duration as varchar),(len(Run_Duration)-1),2) ,2) Run_Duration,
SQL_Message
into _CheckList_Jobs_Failed
from #Result_History_Jobs
where
cast(Run_Date + ‘ ‘ + right(’00’ + substring(Run_time,(len(Run_time)-5),2) ,2)+ ‘:’ +
right(’00’ + substring(Run_time,(len(Run_time)-3),2) ,2)+ ‘:’ +
right(’00’ + substring(Run_time,(len(Run_time)-1),2) ,2) as datetime) >= @ontem + ‘ 17:00’ –dia anterior no horário
and Step_Id = 0
and Run_Status <> 1
order by Dt_Execucao

Enviando
os dados para a planilha e excluindo todas as tabelas utilizadas no
CheckList

-- ABA JOBS FAILED
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\FabricioLima\CheckList\CheckList do Banco de Dados.xls;',
    'SELECT [Job], [Status], [Data Execução], [Tempo Execução], [SQL Message]
    FROM [Jobs Failed$]')
select Job_Name, Status,Dt_Execucao,Run_Duration,SQL_Message
from dbo._CheckList_Jobs_Failed

IF (OBJECT_ID(‘_CheckList_Espacodisco ‘) IS NOT NULL) DROP TABLE _CheckList_Espacodisco
IF (OBJECT_ID(‘_CheckList_Arquivos_SQL’) IS NOT NULL) DROP TABLE _CheckList_Arquivos_SQL
IF (OBJECT_ID(‘_CheckList_Utilizacao_Log’) IS NOT NULL) DROP TABLE _CheckList_Utilizacao_Log
IF (OBJECT_ID(‘_CheckList_Backup’) IS NOT NULL) DROP TABLE _CheckList_Backup
IF (OBJECT_ID(‘_CheckList_JobsRodando’) IS NOT NULL) DROP TABLE _CheckList_JobsRodando
IF (OBJECT_ID(‘_CheckList_Jobs_Failed’) IS NOT NULL) DROP TABLE _CheckList_Jobs_Failed

Depois de gerada a planilha, basta enviá-la por e-mail utilizado o
seu método favorito. Nosso exemplo gerou dados para apenas um
servidor, entretanto essas informações podem ser geradas para vários
servidores. No meu ambiente, visualizo algumas dessas informações de 5
servidores diferentes na mesma aba da planilha, colocando as informações
sobre os diferentes servidores uma abaixo da outra.

Para baixar
um exemplo de como fica a planilha gerada nos scripts passados, clique aqui.

Uma planilha de CheckList pode
conter muito mais informações, isso vai da criatividade e da necessidade
de cada um. Seguem algumas informações que podem ser acrescentadas nessa
planilha:

  • Crescimento de tabelas
  • Crescimento das
    databases
  • Objetos que foram alterados
  • Procedimentos
    mais demorados
  • Fragmentação dos Índices

Nos
próximos artigos mostrarei como obter essas informações.

A geração
dessa planilha pode ser realizada em um pacote do SSIS, onde podem ser
guardados históricos dos arquivos e pode ser gerada uma planilha melhor
formatada, como por exemplo, formatações condicionais para deixar
uma linha de um drive que está com menos de 20% de espaço em disco em
vermelho e negrito.

Espero que essa informação possa ser útil
para alguém assim como é para mim no meu CheckList de DBA. Essa planilha
pode dar um pouco de trabalho para montá-la, entretanto, só é feito uma
vez e a facilidade de análise das informações é muito grande. Com isso,
acredito que vale apena o esforço.

Abraços!