Banco de Dados - SQL Server
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 LimaOlá 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
- Representando dados em XML no SQL ServerSQL Server
- Diferenças entre SEQUENCES x IDENTITY no Microsoft SQL Server 2012SQL
- Utilizando FILETABLE no SQL Server 2012SQL Server
- NHibernate com o Delphi Prism: Acessando um Banco de Dados SQL ServerVisual Studio
- Novidades no SQL Server Codinome DenaliSQL Server