{"id":285,"date":"2010-03-24T09:18:13","date_gmt":"2010-03-24T12:18:13","guid":{"rendered":"http:\/\/fabriciodba.wordpress.com\/2010\/03\/24\/criando-um-checklist-automatico-do-banco-de-dados"},"modified":"2017-05-03T16:42:35","modified_gmt":"2017-05-03T19:42:35","slug":"criando-um-checklist-automatico-do-banco-de-dados","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2010\/03\/24\/criando-um-checklist-automatico-do-banco-de-dados\/","title":{"rendered":"Criando um CheckList Autom\u00e1tico do Banco de Dados"},"content":{"rendered":"
Atualizado em: 01\/05\/2017<\/strong><\/span><\/h5>\n

OBS: Segue abaixo o link de um Post com uma nova vers\u00e3o desse CheckList muito mais pr\u00e1tica e com diversas melhorias. Desta vez, o CheckList ser\u00e1 enviado por e-mail em formato HTML:<\/p>\n

Link Post: Criando um E-mail de CheckList Di\u00e1rio no SQL Server<\/a><\/b>
\n <\/p>\n

\nOl\u00e1 pessoal,<\/p>\n

Assim que assumi a posi\u00e7\u00e3o de DBA, encontrei muitos artigos dizendo que todos os dias deveriam ser realizadas uma serie de verifica\u00e7\u00f5es, o famoso CheckList do DBA, dentre elas, verificar se os Jobs rodaram com sucesso, se os backups foram realizados, se tem espa\u00e7o em disco dispon\u00edvel, 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\u00e1vel, imagine em ambientes maiores que esse (como existem aos montes por ai). Assim, encontrei na internet querys que me retornavam algumas dessas informa\u00e7\u00f5es, mas chegar todos os dias no trabalho e ter que abrir o Management Studio para rodar v\u00e1rias querys ainda n\u00e3o era a solu\u00e7\u00e3o ideal.<\/p>\n

Com isso, resolvi criar uma planilha Excel com v\u00e1rias abas que agrupa todas as informa\u00e7\u00f5es que eu verificava manualmente em um \u00fanico local, com um tipo de informa\u00e7\u00e3o por aba da planilha. Esse relat\u00f3rio \u00e9 enviado diariamente para o meu e-mail as 08:00 da manh\u00e3, logo, quando chego s\u00f3 tenho o trabalho de abrir a planilha, analisar o CheckList e tomar as devidas medidas. Al\u00e9m disso, posso at\u00e9 acompanhar como est\u00e1 o meu banco de dados de casa ou do celular, para o caso de n\u00e3o ter ido ao trabalho (folga, m\u00e9dico, reuni\u00e3o fora da empresa ou f\u00e9rias).<\/p>\n

A planilha de CheckList do banco de dados que ser\u00e1 descrita possui as seguintes abas:<\/p>\n

1 \u2013 Espa\u00e7o em Disco:<\/h6>\n

Nessa aba teremos informa\u00e7\u00f5es sobre como est\u00e3o os drives dos discos do nosso servidor SQL Server. Ela retornar\u00e1 o Drive, o tamanho em MB, o tamanho que est\u00e1 sendo utilizado, o espa\u00e7o livre, o percentual de espa\u00e7o que est\u00e1 sendo utilizado, o percentual dispon\u00edvel, e o espa\u00e7o desse disco que \u00e9 utilizado por arquivos do SQL Server:<\/p>\n\n\n\n
Drive<\/b><\/td>\nTamanho (MB)<\/b><\/td>\nUtilizado(MB)<\/b><\/td>\nLivre (MB)<\/b><\/td>\nUtilizado (%)<\/b><\/td>\nLivre (%)<\/b><\/td>\nOcupado SQL (MB)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

2 \u2013 Arquivos SQL:<\/h6>\n

Nessa aba teremos informa\u00e7\u00f5es 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\u00e1ximo que esse arquivo pode alcan\u00e7ar, o quanto esse arquivo cresce, pr\u00f3ximo tamanho que o arquivo ter\u00e1 e sua situa\u00e7\u00e3o.<\/p>\n\n\n\n
Database<\/b><\/td>\nFile Name<\/b><\/td>\nTamanho(MB)<\/b><\/td>\nTamanho Max(MB)<\/b><\/td>\nCrescimento<\/b><\/td>\nPr\u00f3ximo Tamanho<\/b><\/td>\nSitua\u00e7\u00e3o<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

3 \u2013 Utiliza\u00e7\u00e3o Log:<\/h6>\n

Nessa aba teremos a informa\u00e7\u00e3o do percentual utilizado de todos os arquivos de log existentes no banco:<\/p>\n\n\n\n
Database<\/b><\/td>\nLog Size (MB)<\/b><\/td>\nSpace Used(%)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

4 \u2013 Backup:<\/h6>\n

Uma das abas mais importantes. Nela, saberemos o nome das databases que fizeram o backup com sucesso, o hor\u00e1rio de inicio, a dura\u00e7\u00e3o, o recovery model de cada Database e o tamanho do backup.<\/p>\n\n\n\n
Database<\/b><\/td>\nNome<\/b><\/td>\nInicio<\/b><\/td>\nTempo<\/b><\/td>\nRecovery<\/b><\/td>\nTamanho (MB)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

5 \u2013 Jobs em Execu\u00e7\u00e3o:<\/h6>\n

Muitas vezes me deparei com a situa\u00e7\u00e3o de chegar ao meu ambiente e encontrar v\u00e1rios Jobs rodando (agarrados). Algumas vezes isso s\u00f3 era percebido quase no fim do dia. Para que isso n\u00e3o aconte\u00e7a mais, essa aba retornar\u00e1 todos os Jobs que est\u00e3o executando no momento da gera\u00e7\u00e3o da planilha, no meu caso \u00e0s 8:00 AM . Com essa informa\u00e7\u00e3o, o problema pode ser resolvido rapidamente.<\/p>\n\n\n\n
Job<\/b><\/td>\nData Inicio<\/b><\/td>\nTempo Execu\u00e7\u00e3o<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

6 \u2013 Jobs Failed:<\/h6>\n

Quantas vezes voc\u00ea j\u00e1 identificou um Job que falhou mas estava sem notifica\u00e7\u00e3o? Isso acontece muito em ambientes onde v\u00e1rias pessoas criam e alteram Jobs. Essa aba identificar\u00e1 via query todos os Jobs que falharam mesmo quando o mesmo n\u00e3o enviar nenhuma notifica\u00e7\u00e3o. Ser\u00e1 informado o nome do Job, o status, a data e o tempo de execu\u00e7\u00e3o e a mensagem retornada pelo Job.<\/p>\n\n\n\n
Job<\/b><\/td>\nStatus<\/b><\/td>\nData Execu\u00e7\u00e3o<\/b><\/td>\nTempo Execu\u00e7\u00e3o<\/b><\/td>\nSQL Message<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Apresentadas as abas, vamos ao que interessa, os scripts abaixo preencher\u00e3o a planilha com as informa\u00e7\u00f5es do CheckList. Para baixar essa planilha clique aqui<\/a>.<\/p>\n

O caminho da planilha utilizada nos scripts \u00e9 \u201cC:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls\u201d<\/b>. Esse caminho deve ser alterado para onde a planilha for salva.<\/p>\n

Aba 1: Monitoramento do Espa\u00e7o em Disco<\/h5>\n

Nessa aba criaremos uma procedure que montar\u00e1 uma tabela com todas as informa\u00e7\u00f5es sobre os drives dispon\u00edveis no servidor.<\/p>\n

Para as vers\u00f5es do SQL Server 2005 e 2008, caso a op\u00e7\u00e3o Ole Automation Procedures <\/em>n\u00e3o esteja habilitada em seu servidor, a mesma deve ser habilitada.<\/p>\n

sp_configure 'show advanced options',1\r\nGO\r\nreconfigure\r\nGO\r\nsp_configure 'Ole Automation Procedures',1\r\nGO\r\nreconfigure\r\nGO\r\nsp_configure 'show advanced options',0\r\nGO\r\nreconfigure\r\n<\/pre>\n
Ap\u00f3s habilitada, devemos criar a procedure abaixo em uma determinada database. Segue o script da procedure:<\/p>\n
CREATE PROCEDURE [dbo].[stpVerifica_Espaco_Disco]\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON \r\n\r\n\tCREATE TABLE #dbspace (\r\n\t\t[name] sysname, \r\n\t\t[caminho] VARCHAR(200), \r\n\t\t[tamanho] VARCHAR(10), \r\n\t\t[drive] VARCHAR(30)\r\n\t)\r\n\r\n\tCREATE TABLE [#espacodisco] (\u00a0\u00a0\u00a0 \r\n\t\t[Drive] VARCHAR (10),\r\n\t\t[Tamanho (MB)] INT, \r\n\t\t[Usado (MB)] INT,\r\n\t\t[Livre (MB)] INT, \r\n\t\t[Livre (%)] INT, \r\n\t\t[Usado (%)] INT, \r\n\t\t[Ocupado SQL (MB)] INT,\r\n\t\t[Data] SMALLDATETIME\r\n\t) \r\n\r\n\tEXEC 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]' \r\n\t\r\n\tDECLARE @hr INT, @fso INT, @mbtotal INT, @TotalSpace INT, @MBFree INT, @Percentage INT, @SQLDriveSize INT, @size float, @drive VARCHAR(1), @fso_Method VARCHAR(255) \r\n\r\n\tSET @mbTotal = 0 \r\n\r\n\tEXEC @hr = master.dbo.sp_OACreate 'Scripting.FilesystemObject', @fso OUTPUT \r\n\r\n\tCREATE TABLE #space (\r\n\t\t[drive] CHAR(1), \r\n\t\t[mbfree] INT\r\n\t)\r\n\t\r\n\tINSERT INTO #space \r\n\tEXEC [master].[dbo].[xp_fixeddrives]\r\n\t\r\n\tDECLARE CheckDrives CURSOR FOR SELECT [drive], [MBfree] FROM #space\r\n\tOPEN CheckDrives\r\n\tFETCH NEXT FROM CheckDrives INTO @Drive, @MBFree\r\n\tWHILE( @@FETCH_STATUS = 0 )\r\n\tBEGIN\r\n\t\tSET @fso_Method = 'Drives(\"' + @drive + ':\").TotalSize'\r\n\t\t\r\n\t\tSELECT @SQLDriveSize = SUM(CONVERT(INT, [tamanho]))\r\n\t\tFROM #dbspace \r\n\t\tWHERE SUBSTRING([caminho], 1, 1) = @drive\r\n\t\t\r\n\t\tEXEC @hr = sp_OAMethod @fso, @fso_method, @size OUTPUT\r\n\t\t\r\n\t\tSET @mbtotal =\u00a0 @size \/ (1024 * 1024)\r\n\t\t\r\n\t\tINSERT INTO #espacodisco\r\n\t\tVALUES( @Drive + ':', @MBTotal, @MBTotal - @MBFree, @MBFree, (100 * ROUND(@MBFree, 2) \/ ROUND(@MBTotal, 2)), (100 - 100 * ROUND(@MBFree, 2) \/ ROUND(@MBTotal, 2)), @SQLDriveSize, GETDATE()) \r\n\r\n\t\tFETCH NEXT FROM CheckDrives INTO @drive, @mbFree\r\n\tEND\r\n\tCLOSE CheckDrives\r\n\tDEALLOCATE CheckDrives \r\n\r\n\tIF ( OBJECT_ID('[dbo].[_CheckList_Espacodisco]') IS NOT NULL )\tDROP TABLE [dbo].[_CheckList_Espacodisco]\r\n\r\n\tSELECT [Drive], [Tamanho (MB)], [Usado (MB)], [Livre (MB)], [Livre (%)], [Usado (%)], ISNULL([Ocupado SQL (MB)], 0) AS [Ocupado SQL (MB)]\r\n\tINTO [dbo].[_CheckList_Espacodisco]\r\n\tFROM #espacodisco \r\n\r\n\tDROP TABLE #dbspace\r\n\tDROP TABLE #space\r\n\tDROP TABLE #espacodisco\r\nEND\r\n<\/pre>\n
Agora basta rodar a SP para geramos as informa\u00e7\u00f5es na tabela _CheckList_Espacodisco:<\/p>\n
EXEC [dbo].[stpVerifica_Espaco_Disco]\r\n<\/pre>\n
Para enviar os dados para a planilha, basta executar a query abaixo alterando o caminho da mesma:<\/p>\n
-- ABA ESPA\u00c7O DISCO\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [Drive],\u00a0[Tamanho(MB)], [Utilizado(MB)], [Livre(MB)], [Utilizado(%)], [Livre(%)], [Ocupado SQL(MB)]\u00a0FROM [Espa\u00e7o Disco$]')\r\nSELECT [Drive], [Tamanho (MB)], [Usado (MB)], [Livre (MB)], [Usado (%)], [Livre (%)], [Ocupado SQL (MB)]\r\nFROM [dbo].[_CheckList_Espacodisco]\r\n<\/pre>\n
Segue um exemplo de como essa informa\u00e7\u00e3o ser\u00e1 retornada pela planilha.<\/p>\n\n\n\n\n\n\n
Drive<\/b><\/td>\nTamanho (MB)<\/b><\/td>\nUtilizado(MB)<\/b><\/td>\nLivre (MB)<\/b><\/td>\nUtilizado (%)<\/b><\/td>\nLivre (%)<\/b><\/td>\nOcupado SQL (MB)<\/b><\/td>\n<\/tr>\n
C:<\/td>\n29989<\/td>\n21774<\/td>\n8215<\/td>\n73<\/td>\n27<\/td>\n0<\/td>\n<\/tr>\n
E:<\/td>\n30718<\/td>\n25758<\/td>\n4960<\/td>\n84<\/td>\n16<\/td>\n490<\/td>\n<\/tr>\n
F:<\/td>\n78520<\/td>\n68187<\/td>\n10333<\/td>\n87<\/td>\n13<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Aba 2: Monitoramento dos Arquivos SQL<\/h5>\n
Para a gera\u00e7\u00e3o dos dados dessa aba basta executar o script abaixo:<\/p>\n
IF ( OBJECT_ID('[dbo].[_CheckList_Arquivos_SQL]') IS NOT NULL )\tDROP TABLE [dbo].[_CheckList_Arquivos_SQL]\r\n\r\nCREATE TABLE [dbo].[_CheckList_Arquivos_SQL] (\r\n\t[Name] VARCHAR(250), \r\n\t[FileName] VARCHAR(250), \r\n\t[Size] BIGINT, \r\n\t[MaxSize] BIGINT, \r\n\t[Growth] VARCHAR(100), \r\n\t[Proximo_Tamanho] BIGINT, \r\n\t[Situacao] VARCHAR(15)\r\n) \r\n\r\nINSERT INTO [dbo].[_CheckList_Arquivos_SQL]\r\nSELECT\tCONVERT(VARCHAR, [name]) AS [NAME], \r\n\t\t[Filename],\r\n\t\tCAST([Size] * 8 AS BIGINT) \/ 1024.00 [Size],\r\n\t\tCASE WHEN [MaxSize] = -1 THEN -1 ELSE CAST([MaxSize]\u00a0AS BIGINT)* 8 \/ 1024.00 END [MaxSize],\r\n\t\tCASE \r\n\t\t\tWHEN SUBSTRING(CAST([Status] AS VARCHAR), 1, 2) = 10 THEN CAST([Growth] AS VARCHAR) + ' %'\r\n\t\t\tELSE CAST(CAST(([Growth] * 8 ) \/ 1024.00 AS NUMERIC(15, 2)) AS VARCHAR) + ' MB' \r\n\t\tEND [Growth],\r\n\t\tCASE \r\n\t\t\tWHEN SUBSTRING(CAST([Status] AS VARCHAR), 1, 2) = 10 THEN (CAST([Size] AS BIGINT) * 8 \/ 1024.00) * (([Growth] \/ 100.00) + 1)\r\n\t\t\tELSE (CAST([Size]\u00a0AS BIGINT) * 8 \/ 1024.00) + CAST(([Growth] * 8 ) \/ 1024.00 AS NUMERIC(15, 2))\r\n\t\tEND [Proximo_Tamanho] ,\r\n\t\tCASE \r\n\t\t\tWHEN [MaxSize] = -1 THEN 'OK'\r\n\t\t\tWHEN\r\n\t\t\t( \r\n\t\t\t\tCASE WHEN SUBSTRING(CAST([Status] AS VARCHAR), 1, 2) = 10\r\n\t\t\t\t\tTHEN (CAST([Size] AS BIGINT) * 8 \/ 1024.00) * (([Growth] \/ 100.00) + 1)\r\n\t\t\t\t\tELSE (CAST([Size] AS BIGINT) * 8\/ 1024.00) + CAST(([Growth] * 8 ) \/ 1024.00 AS NUMERIC(15, 2))\r\n\t\t\t\tEND\r\n\t\t\t)\u00a0 <\u00a0 (CAST([MaxSize]\u00a0 AS BIGINT) * 8 \/ 1024.00)\u00a0\r\n\t\t\t\tTHEN\u00a0'OK' ELSE 'PROBLEMA'\r\n\t\tEND [Situacao]\r\nFROM [master]..[sysaltfiles] WITH(NOLOCK)\r\nORDER BY [Situacao], [Size] DESC\r\n<\/pre>\n
Gerada a informa\u00e7\u00e3o, a query abaixo deve ser utilizada para enviar os dados para a planilha.<\/p>\n
-- ABA ARQUIVOS SQL\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [DataBase], [File Name], [Tamanho(MB)], [Tamanho Max(MB)], [Crescimento], [Pr\u00f3ximo Tamanho], [Situacao] FROM [Arquivos SQL$]')\r\nSELECT [Name], [FileName], [Size], [MaxSize], [Growth], [Proximo_Tamanho], [Situacao]\r\nFROM [dbo].[_CheckList_Arquivos_SQL]\r\nORDER BY [Situacao] DESC, [Size] DESC\r\n<\/pre>\n
Quando o valor da coluna Tamanho Max(MB)<\/em> dessa aba da planilha for igual a -1, significa que esse arquivo n\u00e3o possui uma restri\u00e7\u00e3o de crescimento.<\/p>\n
Quando a coluna \u201cSituacao\u201d retornar o valor PROBLEMA, significa que o arquivo n\u00e3o conseguir\u00e1 crescer mais uma vez, logo, esse arquivo de ver diminu\u00eddo ou ter seu tamanho m\u00e1ximo aumentado para que quando ele precise crescer o SQL Server n\u00e3o gere um erro.<\/p>\n
Aba 3: Utiliza\u00e7\u00e3o do Arquivo de Log<\/h5>\n
Para a gera\u00e7\u00e3o dos dados dessa aba, deve ser criada a procedure abaixo que retornar\u00e1 as informa\u00e7\u00f5es sobre os arquivos de log.<\/p>\n
CREATE PROCEDURE [dbo].[StpVerifica_Utilizacao_Log]\r\nAS\r\nDBCC SQLPERF(LOGSPACE) -- N\u00e3o \u00e9 poss\u00edvel inserir em uma tabela direto desse comando\r\n<\/pre>\n
Agora que a procedure j\u00e1 existe, o c\u00f3digo abaixo deve ser executado para gerar a tabela com as informa\u00e7\u00f5es para a planilha.<\/p>\n
IF ( OBJECT_ID('[dbo].[_CheckList_Utilizacao_Log]') IS NOT NULL )\u00a0 DROP TABLE [dbo].[_CheckList_Utilizacao_Log]\r\n\r\nCREATE TABLE [dbo].[_CheckList_Utilizacao_Log] (\r\n\t[Nm_Database] VARCHAR(50),\r\n\t[Log_Size] NUMERIC(15, 2),\r\n\t[Log_Space_Used(%)] NUMERIC(15, 2),\r\n\t[status_log] INT\r\n)\r\n\r\nINSERT INTO [dbo].[_CheckList_Utilizacao_Log]\r\nEXEC [dbo].[StpVerifica_Utilizacao_Log]\r\n<\/pre>\n
Com os dados gerados, basta mandar as informa\u00e7\u00f5es para a planilha.<\/p>\n
-- ABA LOG\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [Database], [Log Size(MB)], [Space Used(%)] FROM [Log$]')\r\nSELECT [Nm_Database], [Log_Size], [Log_Space_Used(%)]\r\nFROM [dbo].[_CheckList_Utilizacao_Log]\r\nORDER BY 3 DESC\r\n<\/pre>\n
 <\/p>\n
Aba 4: Backup<\/h5>\n
Com o script abaixo, teremos a informa\u00e7\u00e3o de todos os backups que foram rodados desde o dia anterior \u00e0s 18h. O intervalo de backup que ser\u00e1 retornado pode ser alterado para ficar de acordo com a realidade do seu ambiente.<\/p>\n
Na vers\u00e3o do SQL Server 2000, a coluna recovery_model<\/em> retornada na query abaixo n\u00e3o existe na tabela backupset,<\/em> logo a query deve ser alterada para retornar um espa\u00e7o em branco nessa coluna.<\/p>\n
IF (OBJECT_ID('[dbo].[_CheckList_Backup]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_Backup]\r\n\r\nCREATE TABLE [dbo].[_CheckList_Backup] (\r\n\t[database_name] NVARCHAR(256),\r\n\t[name] NVARCHAR(256),\r\n\t[backup_start_date] DATETIME,\r\n\t[tempo] INT, \r\n\t[server_name] NVARCHAR(256), \r\n\t[recovery_model] NVARCHAR(120),\r\n\t[tamanho]\u00a0NUMERIC(15, 2)\r\n) \r\n\r\nDECLARE @Dt_Referencia DATETIME\r\nSELECT @Dt_Referencia = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) -- Hora zerada \r\n\r\nINSERT INTO [dbo].[_CheckList_Backup]\r\nSELECT\t[database_name], \r\n\t\t[name],\r\n\t\t[Backup_start_date], \r\n\t\tDATEDIFF(mi, [Backup_start_date], [Backup_finish_date]) [tempo (min)], \r\n\t\t[server_name],\r\n\t\t[recovery_model], \r\n\t\tCAST([backup_size] \/ 1024 \/ 1024 AS NUMERIC(15,2)) [Tamanho (MB)]\r\nFROM [msdb].[dbo].[backupset] B\r\nINNER JOIN [msdb].[dbo].[backupmediafamily] BF ON B.[media_set_id] = BF.[media_set_id]\r\nWHERE\t[Backup_start_date] >=\u00a0DATEADD(hh, 18, @Dt_Referencia - 1) -- Backups realizados a partir das 18h de ontem\r\n\t\tAND [Backup_start_date] < DATEADD(DAY, 1, @Dt_Referencia)\r\n\t\tAND [type] = 'D'\r\n<\/pre>\n
Ap\u00f3s populada a tabela, a query abaixo deve ser utilizada para retornar os dados para a planilha.<\/p>\n
-- ABA BACKUP\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [Database], [Nome], [Start], [Tempo(Min)], [Recovery], [Tamanho(MB)] FROM [Backup$]')\r\nSELECT [database_name], [name], [backup_start_date], [tempo], [recovery_model], [Tamanho]\r\nFROM [dbo].[_CheckList_Backup]\r\nORDER BY [backup_start_date]\r\n<\/pre>\n
 <\/p>\n
Aba 5: Jobs em Execu\u00e7\u00e3o<\/h5>\n
Para verificarmos quais os Jobs que est\u00e3o rodando basta executar o script abaixo. Essa query retorna o nome do Job, o hor\u00e1rio de in\u00edcio e o tempo em minutos que esse Job est\u00e1 executando.<\/p>\n
Para fazer um teste r\u00e1pido, crie e execute um Job com a query waitfor delay ’00:05:00′<\/em><\/strong>. Em seguida rode a query abaixo para retornar esse Job que est\u00e1 sendo executado.<\/p>\n
A informa\u00e7\u00e3o dessa aba n\u00e3o est\u00e1 dispon\u00edvel para a vers\u00e3o do SQL Server 2000, apenas para as vers\u00f5es 2005 e 2008.<\/p>\n
IF ( OBJECT_ID('[dbo].[_CheckList_JobsRodando]') IS NOT NULL )\u00a0 DROP TABLE [dbo].[_CheckList_JobsRodando]\r\n\r\nCREATE TABLE [dbo].[_CheckList_JobsRodando] (\r\n\t[Name] VARCHAR(256),\r\n\t[Data_Inicio] DATETIME,\r\n\t[Tempo_Rodando] INT\r\n)\r\n\r\nINSERT INTO [dbo].[_CheckList_JobsRodando]\r\nSELECT [name], [run_Requested_Date], DATEDIFF(mi, [run_Requested_Date], GETDATE())\r\nFROM [msdb]..[sysjobactivity] A\r\nJOIN [msdb]..[sysjobs] B on A.[job_id] = B.[job_id]\r\nWHERE [start_Execution_Date] IS NOT NULL AND [stop_execution_date] IS NULL\r\n<\/pre>\n
Para enviar as informa\u00e7\u00f5es para a planilha:<\/p>\n
-- ABA JOBS EM EXECU\u00c7\u00c3O\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [Job], [Data Inicio], [Tempo Execu\u00e7\u00e3o] FROM [Jobs em Execu\u00e7\u00e3o$]')\r\nSELECT [Name], [Data_Inicio], [Tempo_Rodando]\r\nFROM [dbo].[_CheckList_JobsRodando]\r\n<\/pre>\n
 <\/p>\n
Aba 6: Jobs que Falharam<\/h5>\n
Geralmente recebemos e-mails e torpedos com os Jobs que falham, entretanto \u00e9 muito importante identificarmos todos os Jobs que falharam por outro meio, pois esse Job pode estar sem notifica\u00e7\u00e3o ou pode ocorrer algum problema com seu servidor de e-mail. A query abaixo retornar\u00e1 todos os Jobs que falharam desde as 17:00 do dia anterior, sendo que esse hor\u00e1rio deve ser configurado de acordo com a necessidade de cada um.<\/p>\n
IF ( OBJECT_ID('Tempdb..#Result_History_Jobs') IS NOT NULL\u00a0)\u00a0\u00a0 DROP TABLE #Result_History_Jobs\r\n\r\nCREATE TABLE #Result_History_Jobs (\r\n\t[Cod] INT IDENTITY(1,1),\r\n\t[Instance_Id] INT, \r\n\t[Job_Id] VARCHAR(255),\r\n\t[Job_Name] VARCHAR(255),\r\n\t[Step_Id] INT,\r\n\t[Step_Name] VARCHAR(255),\r\n\t[Sql_Message_Id] INT,\r\n\t[Sql_Severity] INT,\r\n\t[SQl_Message] VARCHAR(3990),\r\n\t[Run_Status] INT, \r\n\t[Run_Date] VARCHAR(20),\r\n\t[Run_time] VARCHAR(20),\r\n\t[Run_Duration] INT,\r\n\t[Operator_Emailed] VARCHAR(100),\r\n\t[Operator_NetSent] VARCHAR(100),\r\n\t[Operator_Paged] VARCHAR(100),\r\n\t[Retries_Attempted] INT, \r\n\t[Nm_Server] VARCHAR(100)\r\n) \r\n\r\nIF ( OBJECT_ID('[dbo].[_CheckList_Jobs_Failed]') IS NOT NULL )\u00a0 DROP TABLE [dbo].[_CheckList_Jobs_Failed]\r\n\r\nDECLARE @ontem VARCHAR(8)\r\nSET @ontem =\u00a0CONVERT(VARCHAR(8), (DATEADD(DAY, -1, GETDATE())),112) \r\n\r\nINSERT INTO #Result_History_Jobs\r\nEXEC [Msdb].[dbo].[SP_HELP_JOBHISTORY] @mode = 'FULL', @start_run_date =\u00a0@ontem \r\n\r\nSELECT\t[Job_Name],\r\n\t\tCASE\r\n\t\t\tWHEN [Run_Status] = 0 THEN 'Failed'\r\n\t\t\tWHEN [Run_Status] = 1 THEN 'Succeeded'\r\n\t\t\tWHEN [Run_Status] = 2 THEN 'Retry (step only)'\r\n\t\t\tWHEN [Run_Status] = 3 THEN 'Canceled'\r\n\t\t\tWHEN [Run_Status] = 4 THEN 'In-progress message'\r\n\t\t\tWHEN [Run_Status] = 5 THEN 'Unknown' \r\n\t\tEND [Status],\r\n\t\tCAST(Run_Date + ' ' +\r\n\t\t\tRIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 5), 2), 2) + ':' +\r\n\t\t\tRIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 3), 2), 2) + ':' +\r\n\t\t\tRIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 1), 2), 2) AS VARCHAR) [Dt_Execucao],\r\n\t\t\tRIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration]) - 5), 2), 2) + ':' +\r\n\t\t\tRIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration]) - 3), 2), 2) + ':' +\r\n\t\t\tRIGHT('00' + SUBSTRING(CAST([Run_Duration] AS VARCHAR), (LEN([Run_Duration]) - 1), 2), 2) [Run_Duration],\r\n\t\t[SQL_Message]\r\nINTO [dbo].[_CheckList_Jobs_Failed]\r\nFROM #Result_History_Jobs\r\nWHERE\tCAST([Run_Date] + ' ' + RIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 5), 2),2) + ':' +\r\n\t\tRIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 3), 2), 2) + ':' +\r\n\t\tRIGHT('00' + SUBSTRING([Run_time], (LEN([Run_time]) - 1), 2), 2) AS DATETIME) >= @ontem + ' 17:00' -- Dia anterior no hor\u00e1rio\r\n\t\tAND [Step_Id] = 0\r\n\t\tAND [Run_Status] <> 1\r\nORDER BY [Dt_Execucao]\r\n<\/pre>\n
Enviando os dados para a planilha e excluindo todas as tabelas utilizadas no CheckList.<\/p>\n
-- ABA JOBS FAILED\r\nINSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',\r\n'Excel 8.0;Database=C:\\FabricioLima\\CheckList\\CheckList do Banco de Dados.xls;',\r\n'SELECT [Job], [Status], [Data Execu\u00e7\u00e3o], [Tempo Execu\u00e7\u00e3o], [SQL Message] FROM [Jobs Failed$]')\r\nSELECT [Job_Name], [Status], [Dt_Execucao], [Run_Duration], [SQL_Message]\r\nFROM [dbo].[_CheckList_Jobs_Failed]\r\n\r\nIF (OBJECT_ID('[dbo].[_CheckList_Espacodisco]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_Espacodisco]\r\nIF ( OBJECT_ID('[dbo].[_CheckList_Arquivos_SQL]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_Arquivos_SQL]\r\nIF ( OBJECT_ID('[dbo].[_CheckList_Utilizacao_Log]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_Utilizacao_Log]\r\nIF ( OBJECT_ID('[dbo].[_CheckList_Backup]') IS NOT NULL)\tDROP TABLE [dbo].[_CheckList_Backup]\r\nIF ( OBJECT_ID('[dbo].[_CheckList_JobsRodando]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_JobsRodando]\r\nIF ( OBJECT_ID('[dbo].[_CheckList_Jobs_Failed]') IS NOT NULL)\u00a0 DROP TABLE [dbo].[_CheckList_Jobs_Failed]\r\n<\/pre>\n
Depois de gerada a planilha basta envi\u00e1-la por e-mail utilizado o seu m\u00e9todo favorito. Nosso exemplo gerou dados para apenas um servidor, entretanto essas informa\u00e7\u00f5es podem ser geradas para v\u00e1rios servidores. No meu ambiente visualizo algumas dessas informa\u00e7\u00f5es de 5 servidores diferentes na mesma aba da planilha, colocando as informa\u00e7\u00f5es sobre os diferentes servidores uma abaixo da outra.<\/p>\n
Para baixar um exemplo de como fica a planilha gerada nos scripts passados, clique aqui<\/a>.<\/p>\n
Uma planilha de CheckList pode conter muito mais informa\u00e7\u00f5es, isso vai da criatividade e necessidade de cada um. Segue algumas informa\u00e7\u00f5es que podem ser acrescentadas nessa planilha:<\/p>\n