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
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
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:
|
1 |
EXEC [dbo].[stpVerifica_Espaco_Disco] |
Para enviar os dados para a planilha, basta executar a query abaixo alterando o caminho da mesma:
|
1 2 3 4 5 6 |
-- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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.
|
1 2 3 4 5 6 7 |
-- 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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 |
-- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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.
|
1 2 3 4 5 6 7 |
-- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 3 4 5 6 |
-- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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
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 !
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
Valeu Felipe… A centralização dessas informações é realmente uma mão na roda para um DBA…
Valeu alexandre… obrigado pela referência.
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.
Valeu André.
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.
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
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.
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)%’ ;
Perfeita essa Fabrício ! Muito mais alto nível.
Abraços.
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??
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.
ops…linha 32
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.
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
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)
Valeu Ricardo.
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);’)
Olá, obrigado pelo excelente post. Poderia colocar as querys para download ?
Obrigado Jonas.
Acredito que nesse link os scripts estão certinhos para copiar:
http://imasters.com.br/artigo/16733/banco-de-dados/criando-um-checklist-automatico-do-banco-de-dados
No wordpress não fica muito legal.
Abraços.
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
Obrigado Emerson.
Emerson, pode me encaminhar essa planilha por favor ? pelo que me parece o link esta danificado. Segue meu email: [email protected]
Ops, Fabrício. rs
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
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
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]
Obrigado pelo Feedback Gilson.
Abraços
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.
Obrigado Fabio. Que bom que serviu.
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.
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
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.
Obrigado Rafael.
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 ?
É possível sim Vinícius.
Da uma pesquisada na internet sobre Database Mail. Tem vários exemplos de como fazer isso.
Abraços.
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?
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
Boa Tarde Wanderson,
O que quer dizer com objetos inválidos? Não entendi.
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.
Bom dia! Primeiramente parabéns pelo post! Sensacional.
Quando vou executar a proc stpVerifica_Espaco_Disco, aparece o seguinte erro:
“Database ‘SBO’ does not exist. Make sure that the name is entered correctly.”
Poderia me auxiliar?
Bom Dia,
Nesse caso Ariston, só debugando a proc para ver exatamente o ponto que gera o problema para encontrar uma solução.
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!
Boa Tarde Lucas…
Faz muito tempo que não uso esse checklist.
No próximo mês vou liberar uma nova versão desse checklist no corpo do e-mail. Sem Excel.
Aguarde…
Já achei o problema!
Mudei o [SQl_Message] VARCHAR(3990) para [SQl_Message] VARCHAR(max)
Valeu!
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
Olá Rael,
De uma olhada no post abaixo que já possui alguns scripts gratuitos para fazer esse monitoramento.
https://www.fabriciolima.net/blog/2017/05/01/criando-um-e-mail-de-checklist-diario-no-sql-server/
Abraço.
Fabricio
Alguém ainda tem essa planilha?
Já tem uma versão nova no site de cursos.
Free também.