Loading…

Criando um CheckList Automático do Banco de Dados

Atualizado em: 01/05/2017

OBS: Segue abaixo o link de um Post com uma nova versão desse CheckList muito mais prática e com diversas melhorias. Desta vez, o CheckList será enviado por e-mail em formato HTML:

Link Post: Criando um E-mail de CheckList Diário no SQL Server
 

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 em Execução:

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 planilha clique 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çã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('[dbo].[_CheckList_Espacodisco]') IS NOT NULL )	DROP TABLE [dbo].[_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 [dbo].[_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('[dbo].[_CheckList_Arquivos_SQL]') IS NOT NULL )	DROP TABLE [dbo].[_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'
			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) -- Não é 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('[dbo].[_CheckList_Utilizacao_Log]') IS NOT NULL )  DROP TABLE [dbo].[_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 INTO [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('[dbo].[_CheckList_Backup]') IS NOT NULL)  DROP TABLE [dbo].[_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 INTO [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 em Execução

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('[dbo].[_CheckList_JobsRodando]') IS NOT NULL )  DROP TABLE [dbo].[_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('[dbo].[_CheckList_Jobs_Failed]') IS NOT NULL )  DROP TABLE [dbo].[_CheckList_Jobs_Failed]

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 [dbo].[_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('[dbo].[_CheckList_Espacodisco]') IS NOT NULL)  DROP TABLE [dbo].[_CheckList_Espacodisco]
IF ( OBJECT_ID('[dbo].[_CheckList_Arquivos_SQL]') IS NOT NULL)  DROP TABLE [dbo].[_CheckList_Arquivos_SQL]
IF ( OBJECT_ID('[dbo].[_CheckList_Utilizacao_Log]') IS NOT NULL)  DROP TABLE [dbo].[_CheckList_Utilizacao_Log]
IF ( OBJECT_ID('[dbo].[_CheckList_Backup]') IS NOT NULL)	DROP TABLE [dbo].[_CheckList_Backup]
IF ( OBJECT_ID('[dbo].[_CheckList_JobsRodando]') IS NOT NULL)  DROP TABLE [dbo].[_CheckList_JobsRodando]
IF ( OBJECT_ID('[dbo].[_CheckList_Jobs_Failed]') IS NOT NULL)  DROP TABLE [dbo].[_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 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.

 

Gostou desse Post?

Curta, comente, compartilhe…

Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.

Até a próxima.

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

50 thoughts on “Criando um CheckList Automático do Banco de Dados

  1. Muito o bom o artigo, a ideia de colocar essas informações em uma planilha é perfeita.Com isso podemos ter um local centralizado para visualizar a situação de nossas bases de dados, juntando as informações geradas e o potencial do Excel poderíamos gerar relatórios para melhor analisar o ambiente e até mesmo justificar investimentos.Valeu Fabrício, muito bom !

  2. Fabricio,Legal esse primeiro post.. vou fazer uma referencia no meu blog. Eu sempre quis escrever sobre o tema mas falta tempo. Voce detalhou bem legal as tarefas do DBA.Abraços,Alexandre Lopes

  3. Parabéns pelo post Fabrício ! Ele está muito bem explicado e com certeza vai ajudar muita gente que está começando na área de administração de banco MS SQL.

  4. Oi tudo bem!!

    Achei interessante a proposta. Infelizmente não consegui ter sucesso. Consegui criar a SP, a primeira da lista, mas, infelizmente não conseguir gerar a informação para a planilha. Na execução da primeira query deu o seguinte erro:

    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    Se poder me ajudar eu agradeço.
    Um abraço.

    1. Fala Flávio, mandar via OLE DB foi uma forma fácil do pessoal testar em casa, mas muitas pessoas estão enfrantando problema com isso. Sugiro fazer o que eu tenho no meu ambiente, criar um DTS ou um Pacote do SSIS e mandar as tabelas geradas para a planilha via TASKS. No futuro farei um novo post dessa nova forma.

      Abraços

  5. Oi Fabrício,

    Muito bom teu artigo, vou utilizá-lo no meu dia a dia e somente a nível de informação.

    Você criou a procedure StpVerifica_Utilizacao_Log para fazer o comando:

    insert dbo._CheckList_Utilizacao_Log
    exec dbo.StpVerifica_Utilizacao_Log

    Eu já tive a necessidade de testar o espaço do Log e fiz de uma maneira mais simples que foi assim:

    insert dbo._CheckList_Utilizacao_Log
    exec(‘dbcc sqlperf(logspace)’)

    Abraços.

    1. Valeu Marcel. É uma outra forma também.

      Também temos essa maneira abaixo via DMV:

      SELECT db.[name] AS [Database Name] ,
      db.recovery_model_desc AS [Recovery Model] ,
      db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
      ls.cntr_value AS [Log Size (KB)] ,
      lu.cntr_value AS [Log Used (KB)] ,
      CAST(CAST(lu.cntr_value AS FLOAT) /
      case when CAST(ls.cntr_value AS FLOAT) = 0 then 1
      else CAST(ls.cntr_value AS FLOAT) end
      AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
      db.[compatibility_level] AS [DB Compatibility Level] ,
      db.page_verify_option_desc AS [Page Verify Option]
      FROM sys.databases AS db
      INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name
      INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name
      WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’
      AND ls.counter_name LIKE ‘Log File(s) Size (KB)%’ ;

  6. Olá Fabricio!! Tudo bem??

    Qdo rodei o script da procedure CREATE PROCEDURE [dbo].[stpVerifica_Espaco_Disco]
    AS
    BEGIN
    SET NOCOUNT

    Apresentou esse erro:
    Msg 102, Level 15, State 1, Procedure stpVerifica_Espaco_Disco, Line 32
    Incorrect syntax near ‘–’.

    A linha 15 é essa:
    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())

    Tem ideia??

    1. Fala Marcelo,

      Quando você copia a query do wordpress alguns caracteres ficam desformatados.

      Exemplo: Aspas e comentários. Tenta executar o código aos poucos para achar o problema.

      Abraços.

  7. Boa tarde Fabricio,

    Rapaz, quero lhe parabenizar pelo seu esforço em colocar essas dicas e orientações de como organizar e otimizar bases de SQL Server. Éste e outros posts serão muito útil para mim aqui na empresa onde eu trabalho e administro uma base pequena (quase 6GB) e que está muito sobrecarregada.

    Preciso identificar algumas queries que estão lentas no sistema interno nosso e já peguei os scripts que você disponibilizou e irei executá-los em breve aqui e depois lhe digo se consegui ou não.

    Só tenho uma pergunta, antes de tudo: esses scripts que você disponibilizou, daria para executá-los no SQL Server 2000? A nossa base é do SQL Server 2000, pois estamos com um projeto em mente de migrar do 2000 para o 2005 pelo menos, mas teremos de montar um projeto e convencer o nosso Superintendente de que, migrando para uma plataforma mais moderna, teremos ganhos de custos e de tempo, como também de performance, dentreo outras vantagens, mas isso teremos de montar com calma.

    Gostaria de entrar em contato contigo, se possível, por e-mail ou qualquer outro meio, para que possamos trocar idéias. Qualquer coisa, pode mandar e-mail para mim, beleza amigo?

    Muito obrigado pela sua atenção em ler este post gigante.

    1. Valeu José.

      Infelizmente muitos scripts não rodam no SQL 2000 pois não existiam as “SALVADORAS DMV’s”. É fato que o 2005 é muito melhor que o sql 2000.

      Pode me mandar um e-mail. Talvez eu demore um pouco para responder devido ao tempo ser curto, mas um dia respondo. rsrs

      Qualquer dúvida mais urgente, o forum do technet tem MVP’s que sempre respondem bem rápido as perguntas.

      Abraço

  8. Fabrício, refiz a query para pegar informações dos files com algumas modificações.
    Abraços,
    Ricardo Leal

    select
    [NomeLogico]=convert(varchar, name),
    [NomeFisico]=Filename,
    [TamanhoMB] =cast(Size/128.00 as numeric(10,2)), — 8/1024 = 1/128
    [TamanhoMax]=case when MaxSize = -1 then ‘ILIMITADO’
    when MaxSize = 0 then ‘SEM CRESCIMENTO’
    when MaxSize > 1310720 then ‘LIMITADO A ‘+ cast(cast(MaxSize/(128.0*1024.0*1024.0) as numeric(10,2)) as varchar(30))+’TB’
    when MaxSize > 131072 then ‘LIMITADO A ‘+ cast(cast(MaxSize/(128.0*1024.0) as numeric(10,2)) as varchar(30))+’GB’
    else ‘LIMITADO A ‘+ cast(cast(MaxSize/128.0 as numeric(10,2)) as varchar(30))+’MB’
    end,
    [Crescimento]=case when substring(cast(Status as varchar),1,2) = 10 then cast(Growth as varchar) + ‘ %’
    else cast (cast(Growth/128.00 as numeric(15,2)) as varchar) + ‘ MB’
    end,
    [Proximo_TamanhoMB]=case when substring(cast(Status as varchar),1,2) = 10
    then cast((Size/128)*((Growth/100.00) + 1) as numeric(10,2))
    else cast((Size + Growth)/128 as numeric(10,2))
    end,
    [SituacaoProxTamanho]=case when MaxSize = -1 then ‘OK’
    when( case when substring(cast(Status as varchar),1,2) = 10
    then Size * ((Growth/100.00) + 1)
    else Size + Growth
    end) < MaxSize then 'OK'
    else 'PROBLEMA'
    end
    from master..sysaltfiles with(nolock)

  9. Fabrício,
    consegui armazenar a saída do DBCC com o procedimento abaixo:

    CREATE TABLE #BancosLog (nome varchar(256) PRIMARY KEY,
    dblogsize varchar(13) NULL,
    dbperclogsize varchar(13) NULL,
    estado bit null)

    INSERT #BancosLog
    exec (‘DBCC SQLPERF(LOGSPACE);’)

  10. Olá Fabricio, andei tentando fazer um modelo como esses para ajudar nas minhas tarefas diárias com 3 bd SQL Server..Encontrei esse post de ótima qualidade parabéns cara.

    Att

    Emerson

  11. Boa Tarde, Fabricio!

    Tenho um servidor rodando o SQL Server 2008 com Windows Server 2008 x64 e instalado o Office 2007, porem quando rodo esse script abaixo, apresenta essa falha, tem idéia de como resolve?

    Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered.

    Script que rodo para dar essa mensagem acima

    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

    1. Fala Marcelo,

      Acho que isso não funciona no 64 bits…

      Mandar via OLE DB foi uma forma fácil do pessoal testar em casa, mas muitas pessoas estão enfrantando problema com isso. Sugiro fazer o que eu tenho no meu ambiente, criar um DTS ou um Pacote do SSIS e mandar as tabelas geradas para a planilha via TASKS.

      Abraços

  12. Fala Fabricio / Fala Marcelo…,

    Também enfrentei o problema do Marcelo…, se não estou enganado o erro acima do Marcelo, é por ele não ter instalado o Driver OLE DB no servidor…, mas como o driver Jet OLEDB não tem suporte para 64 bits, ele não vai conseguir usar com esse driver.

    Para conseguir usar, foi necessário a instalação do ACESS.OLEDB.12.0…, você consegue baixá-lo no link abaixo.

    Você também terá que remover o Office 32 Bits para conseguir fazer a instalação…, e se você preciar usar o excel nesse servidor, você vai precisar da instalaçã do office de 64 bits.

    Segue os passos que você deve seguir:

    1) Download our 64bit version (AccessDatabaseEngine_X64.exe) of our “Microsoft Access Database Engine 2010 Redistributable” from the URL below :

    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    2) Install AccessDatabaseEngine_X64.exe on your SQL Server machine

    3) Open a new “Query Window” in SQL Server Management Studio (SSMS) after connecting to your SQL Server Engine and issue the T-SQL commands below :

    USE [master]
    GO
    sp_configure ‘show advanced options’, 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 1
    GO
    RECONFIGURE WITH OverRide
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1
    GO

    4) Now try executing the query below :
    select * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:\temp\test.xls’, [Sheet1$])

    No nosso caso teremos que executar a query abaixo:

    — ABA ARQUIVOS SQL — COM PROVIDER ACCESS
    INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=D:\CheckListBD\CheckList do Banco de Dados.xls’, [ArquivosSQL$])
    select Data, Databasename, Name,FileName, groupid, Size,MaxSize,Growth,Proximo_Tamanho,Situacao
    from dbo.CheckList_Arquivos_SQL
    order by Situacao desc, Databasename, groupid,Size desc

    SELECT *
    FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=D:\CheckListBD\CheckList do Banco de Dados.xls;’,[ArquivosSQL$])

    No meu caso funcionou corretamente…, só não cosegui usar a exclusão de linhas….

    Espero ter ajudado, não me lembro em qual forum eu consegui… se não deixaria aqui os créditos…, mas se não me engano também ja vi esse problema ser comentado pelo ” Nilton Pinheiro “.

    Abraços,
    Gilson Souza
    [email protected]

  13. Cara, sem dúvidas um excelente post, já fiz algumas alterações baseadas na minha necessidade e rodou perfeitamente, parabéns pelo post, continue assim.

  14. Ola Fabricio, otimo site… um dos melhores que encontrei quando, estais nos ajudando muito, obrigado. Quanto ao verificacao de espaco em disco, estou com o erro abaixo :

    “Nível máximo de aninhamento de procedimento armazenado, função, gatilho ou exibição excedido (o limite é 32).”

    O que pode ser. Estou utilizando SQL 2008R2 STD.

  15. Fabricio, sem querer ser repetitivo, parabéns pelo seu blog.
    achei tao legal esse post que resolvi implementar no sistema um formulario que mostra em guias separadas as informacoes que voce colocou aqui entre outras coletadas no seu site.

    teve uma que nao ta dando certo:
    e a sp stpVerifica_Espaco_Disco

    apenas as colunas DRIVE, Livre(MB) e Ocupado SQL(MB) Retornam informacoes
    as demais Retornam NULO
    ja revisei to codigo varias vezes e ta igualzinho o seu (eu acho)

    outra coisa e que nao consegui colocar para exportar para excel, ja tentei de tudo.
    ja rodei sp_configure ‘Ole Automation Procedures’,1
    rodei tambem sp_configure ‘Ad Hoc Distributed Queries’,1

    ja instalei um pacote ACCESSDABASEENGINE.EXE

    ta tentei assim: (para testes)

    select * from openrowset(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0 Xml;Database=C:\rad\CheckList do Banco de Dados.xls;’,’Select * from [Espaço Disco$]’)

    SELECT * FROM OPENDATASOURCE( ‘Microsoft.ACE.OLEDB.12.0’, ‘Data Source=”C:\rad\CheckList do Banco de Dados.xls”;Extended properties=Excel 8.0’)…[Espaço Disco$]

    Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”

    tenho instalado sql 2012 express 64bits v11.0.2100 win7 64bits
    grato por sua atenção
    Wilton

  16. Fabrício,

    Muito bom o forum. Todas as tarefas que eu criei e coloquei a rodar em Jobs funcionam perfeitamente após alguns ajustes. Consegui fazer até algumas mudanças criando um banco de dados chamado checklist onde eu coloquei todos os objetos. Com isso ficou mais fácil de implementar o checklist em outros ambientes.

  17. Fabricio,

    Muito bom esse CheckList vai me ajudar muito aqui, mas gostaria de tirar uma duvida com voce, na questao de monitorar o espaço em disco ao invés de gravar os dados em uma planilha como eu conseguiria fazer para que o SQL apos um select na tabela enviasse um alerta por email.
    Ex: ele faria um select na tabela e caso o valor de % do disco estive acima de 90% ele dispara um email usando o Alerta.
    É possivel ?

      1. Consegui fazer Fabricio, ficou bacana consigo agora monitorar os espaços nos disco de acordo com um valor que determino e se qualquer disco atingir esse valor ele ja me dispara um email.
        Estou com um duvida quanto a procedure SP_MSForEachDB, pelo que entendi dela passado o parametro ela analisa todas as bases e retorna os valores, porem tem uma base em um servidor que fizeram o favor de nomeá-la com um “.” no meio do nome dai ja viu a dor de cabeça, e na hora que essa procedure roda ela nao entende o ponto como parte do nome ela entende que acaba o nome antes do ponto e nisso nao acha a base, como posso faze-la entender isso?
        Pelo que percebi ele pega o nome da base mas de uma tabela que nao tem essa coluna…achei meio estranho teria como vc explicar como funciona esse parametro?

  18. Fabrício,

    Como eu faço para verificar se o banco de dados tem objetos inválidos?

    E caso tenha objetos inválidos, como eu faço para recompilá-los?

    Att,

    Wanderson

      1. Sei que o post é antigo, mas a dúvida do wanderson deve ser porque no Oracle uma stored procedure pode ficar inválida por n motivos. Por sua vez existe um comando que recompila todas elas. O que é diferente no SQLSERVER.

  19. Fabrício, boa tarde! Parabens pelo seu trabalho e pelo post.
    Sótive um pequeno problema para criar a table #Result_History_Jobs.

    Msg 8152, Level 16, State 2, Procedure sp_help_jobhistory_full, Line 62
    Dados de cadeia ou binários seriam truncados.

    Na chamada da proc [SP_HELP_JOBHISTORY], não parece ter nada errado. Você tem alguma ideia? Obrigado!

  20. Gostei muito desse post, esta me ajudando bastante
    gostaria de saber como armazenar um historico do espaço em disco, na tabela [_CheckList_Espacodisco] fica armazenado somente as informações do banco quando a procedure roda, mas gostaria de saber como armazenar um historico.
    Não sei se vc tem um post sobre isso

Deixe uma resposta para José Pereira dos Anjos JuniorCancelar resposta

%d blogueiros gostam disto: