Há algum tempo atrás eu li um artigo do Fabricio Lima (Criando um CheckList Automático do Banco de Dados ), adaptei os scripts dele as minhas necessidades e montei um relatório no Reporting Services, segue passo a passo o relatório que recebo diariamente por e-mail.
Para desenvolver um relatório é necessário ter instalado o Business Intelligence Development Studio, caso já tenha crie um projeto do tipo “Report Server Project”
Agora precisamos das fontes de dados compartilhadas, na aba Solution explorer clique com o botão direito em cima da pasta “Shared Data Sources” e selecione a opção “Add New Data Source”.
Defina as propriedades da conexão com o banco de dados.
Agora é necessário criar os DataSet de acordo com a sua necessidades, segue abaixo alguns
exemplos:
Para obtermos o tamanho das bases de dados da instância utilizamos a procedure abaixo.
— =============================================
— Author: <Andressa>
— Create date: <18/02/2011>
— Description: <retorna dados do espaço utilizado pelas bases>
— =============================================
CREATE PROCEDURE [dbo].[usp_Relatório_DBCheckList]
AS
BEGIN
DECLARE @tabela TABLE (Nome NVARCHAR(128),Tamanho float,NaoAlocado float)
DECLARE AllDatabases CURSOR FOR
SELECT name
FROM sys.databases
WHERE STATE = 0
OPEN AllDatabases
DECLARE @DBNome NVARCHAR(128),@SQL NVARCHAR(MAX)
FETCH NEXT FROM AllDatabases INTO @DBNome
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = N’USE ‘ + @DBNome +’
declare @objname nvarchar(776), @id int ,@type character(2) ,@pages bigint ,
@dbname sysname ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,
@usedpages bigint ,@rowCount bigint
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
end
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
”unallocated space” = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) ) ‘
INSERT INTO @tabela
EXEC sp_executesql @SQL
FETCH NEXT FROM AllDatabases INTO @DBNome
END
SELECT Nome, Tamanho, NaoAlocado from @tabela
CLOSE AllDatabases
DEALLOCATE AllDatabases
END
Agora referencie a procedure no dataset.
Tamanho das unidades de Disco do servidor (DataSet_Disco)
XP_FIXEDDRIVES
Tamanho dos arquivos de log de toda a instância (DataSet_LogSpace)
DBCC SQLPERF(LOGSPACE)
Listagem de Jobs que estão em execução (DataSet_JobsExecucao)
SELECT Name AS Nome
,run_Requested_Date AS DataExecucao
,DATEDIFF(mi,run_Requested_Date
,GETDATE()) AS Duracao_Minutos
FROM msdb..sysjobactivity A INNER JOIN
msdb..sysjobs B ON A.job_id = B.job_id
WHERE start_Execution_Date IS NOT NULL AND stop_execution_date IS NULL
Listagem dos Jobs que não foram executados com sucesso (DataSet_JobFailed)
DECLARE @Ontem INT
DECLARE @OntemHorario INT
SET @OntemHorario= CAST((RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(HOUR,DATEADD(HOUR,-1, GETDATE())))), 2) +RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(MINUTE,GETDATE()))), 2)+RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(SECOND,GETDATE()))), 2)) AS INT)
SET @Ontem = CAST(CONVERT (VARCHAR(8),(DATEADD (DAY, -1, GETDATE())),112) AS INT)
CREATE TABLE #HistoricoJobs( Cod INT identity(1,1)
,IDInstancia INT
,IDJob VARCHAR(255)
,NomeJob VARCHAR(255)
,IDStep INT
,NomeStep VARCHAR(255)
,IDSqlMensagem INT
,SqlSeverity INT
,SqlMensangem VARCHAR(3990)
,IDStatusExecucao INT
,DataExecucao VARCHAR(20)
,HorarioExecucao VARCHAR(20)
,DuracaoExecucao INT
,EmailOperador VARCHAR(100)
,NetSenderOperador VARCHAR(100)
,PagerOperador VARCHAR(100)
,TentativasRealizadAS INT
,NomeServidor VARCHAR(100))
INSERT INTO #HistoricoJobs
EXEC Msdb.dbo.SP_HELP_JOBHISTORY NULL,NULL,NULL,NULL,NULL,@Ontem,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’FULL’
SELECT NomeJob
,CASE
WHEN IDStatusExecucao = 0 THEN ‘Failed’
WHEN IDStatusExecucao = 1 THEN ‘Succeeded’
WHEN IDStatusExecucao = 2 THEN ‘Retry (step only)’
WHEN IDStatusExecucao = 3 THEN ‘Canceled’
WHEN IDStatusExecucao = 4 THEN ‘In-progress message’
WHEN IDStatusExecucao = 5 THEN ‘Unknown’ END Status
,CAST(DataExecucao + ‘ ‘ + RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-5),2) ,2)+ ‘:’ +
RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-3),2) ,2)+ ‘:’ +
RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-1),2) ,2) AS VARCHAR) Dt_Execucao
,RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-5),2) ,2)+ ‘:’ +
RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-3),2) ,2)+ ‘:’ +
RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-1),2) ,2) DuracaoExecucao
,SqlMensangem
FROM #HistoricoJobs
WHERE ((DataExecucao >=@Ontem AND HorarioExecucao >= @OntemHorario) OR DataExecucao >@Ontem)
AND IDStep = 0
AND IDStatusExecucao <> 1
ORDER BY Dt_Execucao
DROP TABLE #HistoricoJobs
Listagem dos últimos backups (DataSet_Backup)
SELECT database_name as NomeDataBase
, name as Nome
,Backup_start_date as DataInicio
, datediff(mi,Backup_start_date,Backup_finish_date) Duracao_Min
, server_name as NomeServidor
,recovery_model as RecoveryModel
, cast(backup_size/1024/1024 as numeric(15,2)) As Tamanho
,type as Tipo
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)
order by database_name, Backup_start_date
/
Após criar os DataSets você terá uma lista semelhante a imagem abaixo:
Para criar um relatório clique com o botão direito em cima da pasta report e “New Item”, selecione “report”, nomeie o arquivo e clique em “Add”. Feito isso você precisa criar os DataSources/DataSets utilizando o DataSource/DataSet reference correspondente.
Com a parte de acesso a dados já finalizada, precisamos criar a parte gráfica do relatório, montei umas tabelas simples e sem formatação apenas para o melhor entendimento.
Para adicionar um campo à uma tabela é simples, somente arrastar o campo do dataset para a tabela desejada.
Abaixo eu apliquei uma formatação simples apenas para diferenciar os dados do nosso relatório (job, bkp, log…)
Utilizei um recurso opcional de alterar a cor da célula de acordo com o valor da linha, assim quando um banco de dados estiver com pouco espaço alocado eu posso visualizar de maneira mais rápida facilitando a tomada de decisão, segue o comando que eu montei.
=IIF(
((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.9,
"#f28989",
IIF((((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.8
and ((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value) < 0.9),
"#ffff80",
"#c0ffc0"))
Você pode adaptar a expressão acima para toda as tabelas.
Interpretação do código:
Quando o espaço alocado do meu banco de dados for…
- maior que 90% a linha fica vermelha.
- maior que 80% e menor que 90% a linha fica amarela.
- menor que 80% a linha fica verde.
Vejam como ficou o relatório depois de renderizado.
O projeto de relatórios está disponível pra
download.
Espero que tenham gostado da dica.
Andressa
#VirtualPassBR
SQL GO!!! 