Criando um CheckList Automático do Banco de Dados

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, dentre elas, verificar se os Jobs rodaram com sucesso, se os backups foram realizados, se tem espaço em disco disponível, etc.

por Fabrício França Lima

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, dentre elas, verificar se os Jobs rodaram com sucesso, se os backups foram realizados, se tem espaço em disco disponível, etc. No meu ambiente cuido de 5 servidores com SQL Server e realizar essa tarefa em cada um desses servidores me toma um tempo razoável, imagine em ambientes maiores que esse (como existem aos montes por ai). 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 Excel 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 as 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 (folga, médico, reunião fora da empresa ou férias).

A planilha de CheckList do banco de dados que será descrita possui as seguintes abas:

1 –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)

2 –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

3 –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(%)

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

Database

Nome

Inicio

Tempo

Recovery

Tamanho (MB)

5 –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

6 –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 planilhaclique aqui.

O caminho da planilha utilizada 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çãoOle Automation Proceduresnã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 colunaTamanho 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 colunarecovery_modelretornada na query abaixo não existe na tabelabackupset,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 querywaitfor 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: Jobs 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 necessidade de cada um. Segue 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 posts 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 condicionaispara 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,

Fabrício França Lima

MCITP - Database Administrator

http://fabriciodba.spaces.live.com/

http://twitter.com/fabriciodba