Aplicação Web para ver os SQL Server Agent Jobs de vários servidores

Problema: 
Tenho vários servidores em produção onde correr jobs críticos e preciso de ver todos os dias se correram bem, se tenho sobreposição na execução dos jobs e quando é que os jobs vão ser executados novamente.

Solução: Com base nas queries que mostrei nos posts anteriores, criei uma aplicação web em MVC no Github que resolve o problema.

• Query para obter histórico dos Jobs no SQL Server Agent
• Query para obter Jobs a executar no SQL Server Agent
• Query para obter a data da próxima execução dos Jobs no SQL Server Agent

A aplicação está neste URL

• https://github.com/rmlalves/sql-agent-schedule-view

Para a usar é preciso configurar os servidores na secção connectionStrings do web.config e depois quando correm a aplicação é só seleccionar os servidores na secção Servers para ver o agendamento agregado e as datas da próxima execução.

O código é disponibilizado as is, actualmente uso numa aplicação para saber o que acontece em produção nos vários servidores com mais uns pózinhos específicos para o meu caso.

Se ajudar fixe, mas não dou garantias de nada :)

Query para obter a data da próxima execução dos Jobs no SQL Server Agent

Esta query basicamente retorna o que vemos no "Job Activity Monitor" na coluna "Next Run" do SQL Server Agent


A query retorna as seguintes colunas:

• JobID: Guid que identifica o Job
• Name: Nome do Job
• StartDate: Data/hora em que o Job vai executar

1 2 3 4 5 6 7 8 9 10

SELECT sysjobs.job_id AS JobId ,sysjobs.name AS Name ,MAX(sysjobactivity.next_scheduled_run_date) AS StartDate FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobactivity ON (sysjobactivity.job_id = sysjobs.job_id) WHERE sysjobactivity.next_scheduled_run_date > GETDATE() GROUP BY sysjobs.job_id ,sysjobs.name

Query para obter Jobs a executar no SQL Server Agent

Esta query basicamente retorna o que vemos no "Job Activity Monitor" do SQL Server Agent com mais uns pózinhos :)


A query retorna as seguintes colunas:

• JobID: Guid que identifica o Job
• Name: Nome do Job
• StartDate: Data/hora em que o Job começou
• EndDate: Data/hora em que o Job terminou
• Duration: Duração do Job em segundos
• ResultStatus: Resultado da execução do Job
• ResultMessage: Mensagem com os detalhes do resultado da execução do Job

1 2 3 4 5 6 7 8 9 10 11 12

SELECT sysjobs.job_id AS JobID ,sysjobs.name AS Name ,sysjobactivity.start_execution_date AS StartDate ,GETDATE() AS EndDate ,DATEDIFF(SECOND, sysjobactivity.start_execution_date, GETDATE()) AS Duration ,'Running' AS ResultStatus ,'The job is running...' AS ResultMessage FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobactivity ON (sysjobactivity.job_id = sysjobs.job_id) WHERE sysjobactivity.job_history_id IS NULL AND sysjobactivity.start_execution_date IS NOT NULL

Query para obter histórico dos Jobs no SQL Server Agent

Esta query basicamente retorna o que vemos no "View History" do SQL Server Agent com mais uns pózinhos :)


A query retorna as seguintes colunas:

• JobID: Guid que identifica o Job
• Name: Nome do Job
• StartDate: Data/hora em que o Job começou
• EndDate: Data/hora em que o Job terminou
• Duration: Duração do Job em segundos
• ResultStatus: Resultado da execução do Job
• ResultMessage: Mensagem com os detalhes do resultado da execução do Job

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

SELECT sysjobs.job_id AS JobID ,sysjobs.name AS Name ,CAST( CAST(sysjobhistory.run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS StartDate ,DATEADD(HOUR, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT) ,DATEADD(MINUTE, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT) ,DATEADD(SECOND, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT) ,CAST( CAST(sysjobhistory.run_date AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) ) ) ) AS EndDate ,( CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT) * 3600 -- HOURS + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT) * 60 -- MINUTES + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT) -- SECONDS ) AS Duration ,CASE WHEN sysjobhistory.run_status = 0 THEN 'Failed' WHEN sysjobhistory.run_status = 1 THEN 'Success' WHEN sysjobhistory.run_status = 4 THEN 'Running' ELSE 'Others (' + CAST(sysjobhistory.run_status AS VARCHAR) + ')' END AS ResultStatus ,sysjobhistory.[message] AS ResultMessage FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobhistory ON (sysjobhistory.job_id = sysjobs.job_id) WHERE sysjobhistory.step_id = 0 -- GET JOB RESULT

Formatador de T-SQL "para os pobres"

Um developer deve sempre usar um conjunto de coding standards de forma a que o código siga sempre as mesmas convenções facilitando a sua leitura e compreensão.

A nível do Visual Studio nunca tive muitos problemas, mas a nível do T-SQL ... a historia é outra. Uma ferramenta que ajuda e muito é o addin "Poor Mans T-SQL" que implementa os coding standards definidos por Steven Bates numa série de posts nos MSDN Blogs.

Este addin está disponível neste link de várias formas: Stand alone app, Notepad++ addin, online, ...

Depois de instalado basta abrir/reiniciar o SQL Server Management Studio, e o addin está disponível no menu Tools.

Fonte: Poor Mans T-SQL formatter

Fazer queries a xml com namespaces no SQL

Imaginemos que temos uma tabela onde guardamos um livro em formato xml numa coluna:

 DECLARE @book TABLE  
 (  
  BookID int,   
  Details xml  
 );  
   
 INSERT INTO @book  
 VALUES (1,   
 '<book id="bk101" xmlns="http://rmlalves.blogspot.com/">  
   <author>Gambardella, Matthew</author>  
   <title>XML Developer''s Guide</title>  
   <genre>Computer</genre>  
   <price>44.95</price>  
   <publish_date>2000-10-01</publish_date>  
   <description>An in-depth look at creating applications with XML.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (2,   
 '<book id="bk102" xmlns="http://rmlalves.blogspot.com/">  
   <author>Ralls, Kim</author>  
   <title>Midnight Rain</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2000-12-16</publish_date>  
   <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (3,   
 '<book id="bk103" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>Maeve Ascendant</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2000-11-17</publish_date>  
   <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (4,   
 '<book id="bk104" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>Oberon''s Legacy</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2001-03-10</publish_date>  
   <description>In post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (5,   
 '<book id="bk105" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>The Sundered Grail</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2001-09-10</publish_date>  
   <description>The two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon''s Legacy.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (6,   
 '<book id="bk106" xmlns="http://rmlalves.blogspot.com/">  
   <author>Randall, Cynthia</author>  
   <title>Lover Birds</title>  
   <genre>Romance</genre>  
   <price>4.95</price>  
   <publish_date>2000-09-02</publish_date>  
   <description>When Carla meets Paul at an ornithology conference, tempers fly as feathers get ruffled.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (7,   
 '<book id="bk107" xmlns="http://rmlalves.blogspot.com/">  
   <author>Thurman, Paula</author>  
   <title>Splish Splash</title>  
   <genre>Romance</genre>  
   <price>4.95</price>  
   <publish_date>2000-11-02</publish_date>  
   <description>A deep sea diver finds true love twenty thousand leagues beneath the sea.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (8,   
 '<book id="bk108" xmlns="http://rmlalves.blogspot.com/">  
   <author>Knorr, Stefan</author>  
   <title>Creepy Crawlies</title>  
   <genre>Horror</genre>  
   <price>4.95</price>  
   <publish_date>2000-12-06</publish_date>  
   <description>An anthology of horror stories about roaches, centipedes, scorpions and other insects.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (9,   
 '<book id="bk109" xmlns="http://rmlalves.blogspot.com/">  
   <author>Kress, Peter</author>  
   <title>Paradox Lost</title>  
   <genre>Science Fiction</genre>  
   <price>6.95</price>  
   <publish_date>2000-11-02</publish_date>  
   <description>After an inadvertant trip through a Heisenberg Uncertainty Device, James Salway discovers the problems of being quantum.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (10,   
 '<book id="bk110" xmlns="http://rmlalves.blogspot.com/">  
   <author>O''Brien, Tim</author>  
   <title>Microsoft .NET: The Programming Bible</title>  
   <genre>Computer</genre>  
   <price>36.95</price>  
   <publish_date>2000-12-09</publish_date>  
   <description>Microsoft''s .NET initiative is explored in detail in this deep programmer''s reference.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (11,   
 '<book id="bk111" xmlns="http://rmlalves.blogspot.com/">  
   <author>O''Brien, Tim</author>  
   <title>MSXML3: A Comprehensive Guide</title>  
   <genre>Computer</genre>  
   <price>36.95</price>  
   <publish_date>2000-12-01</publish_date>  
   <description>The Microsoft MSXML3 parser is covered in detail, with attention to XML DOM interfaces, XSLT processing, SAX and more.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (12,   
 '<book id="bk112" xmlns="http://rmlalves.blogspot.com/">  
   <author>Galos, Mike</author>  
   <title>Visual Studio 7: A Comprehensive Guide</title>  
   <genre>Computer</genre>  
   <price>49.95</price>  
   <publish_date>2001-04-16</publish_date>  
   <description>Microsoft Visual Studio 7 is explored in depth, looking at how Visual Basic, Visual C++, C#, and ASP+ are integrated into a comprehensive development environment.</description>  
 </book>');   

Tendo esta tabela agora queremos fazer queries sobre o xml, para isso o sql tem um conjunto de métodos de ajuda para podermos aplicar uma expressão xpath à coluna xml.

Em conjunto com esses métodos também temos um helper para indicar à query os xml namespaces a usar.

 WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)  
 SELECT  
   Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,  
   Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,  
   Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,  
   Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price  
 FROM @book  

 WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)  
 SELECT  
   Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,  
   Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,  
   Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,  
   Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price  
 FROM @book  
 WHERE Details.value('(/cnt:book/cnt:price)[1]', 'money') > 40  

Fonte: Add Namespaces to Queries with WITH XMLNAMESPACES

Reconstruir e reorganizar todos os índices duma base de dados

Um dos problemas que muitas vezes encontro é nas bases de dados de desenvolvimento/qualidade tabelas com índices fragmentados, o que faz com que as pesquisas sejam muito lentas e algumas vezes surjam deadlocks.

Uma forma de resolver o problema é ir tabela a tabela, índice a índice e fazer rebuild pelo SQL Server Management Studio. Mas isto é uma tarefa demorada e "chata" o que faz com que muitas vezes na realidade ninguém o faça :)

Outra forma de fazer é ter um script que faz isto automaticamente, este script que mostro de seguida faz isso mesmo. Ele vai tabela a tabela avaliar a fragmentação de cada índice e baseado nas recomendações da Microsoft se a fragmentação for maior que 30% faz um REBUILD senão se for entre 5% e 30% faz REORGAZINE.

NOTA 1: O script pode demorar algum tempo a executar e pode bloquear tabelas.

NOTA 2: "There are no silver bullets" isto ajuda as queries a ficarem mais rápidas ... ou não, nada substitui o bom desenho\implementação da base de dados e se o problema for estrutural não será uma manutenção dos índices que o vai resolver.

-- http://technet.microsoft.com/en-us/library/ms189858.aspx
-- Microsoft Recommends
-- REBUILD IF > 30% fragmented
-- REORGANIZE IF > 5% and < 30% fragmented

DECLARE 
    @schemaName NVARCHAR(255),
    @tableName NVARCHAR(255),
    @indexName NVARCHAR(255),
    @sql NVARCHAR(2000),
    @fragmentation FLOAT;

DECLARE TableCursor CURSOR FOR
SELECT DISTINCT
    schemas.name AS SchemaName,
    tables.name AS TableName,
    indexes.name AS IndexName,
    dm_db_index_physical_stats.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tablename'), NULL, NULL, NULL)
INNER JOIN sys.indexes ON (dm_db_index_physical_stats.OBJECT_ID = indexes.OBJECT_ID AND dm_db_index_physical_stats.index_id = indexes.index_id)
INNER JOIN sys.index_columns ON (indexes.OBJECT_ID = index_columns.OBJECT_ID AND indexes.index_id = index_columns.index_id)
INNER JOIN sys.columns ON (index_columns.OBJECT_ID = columns.OBJECT_ID AND index_columns.column_id = columns.column_id)
INNER JOIN sys.tables ON (indexes.OBJECT_ID = tables.OBJECT_ID)
INNER JOIN sys.schemas ON (tables.schema_id = schemas.schema_id)

OPEN TableCursor
FETCH NEXT FROM TableCursor 
INTO @schemaName, @tableName, @indexName, @fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@fragmentation > 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REBUILD;'
        EXEC (@sql)
    END
    ELSE IF (@fragmentation BETWEEN 5 AND 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REORGANIZE;'
        EXEC (@sql)
    END

    FETCH NEXT FROM TableCursor 
    INTO @schemaName, @tableName, @indexName, @fragmentation
END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

Fonte: Reorganize and Rebuild Indexes

Queries da Entity Framework 5+ a correr na base de dados master

Se criarem um edmx de Entity Framework 5/6 e depois meterem o Sql Profiler a correr verificam que as queries não estão a correr na base de dados "correcta", elas estão a correr todas a partir da master.

Isto não impede em nada o funcionamento das aplicações (pois todas as queries vêm com as tabelas prefixadas da base de dados correcta) mas se tivermos múltiplas bases de dados torna-se complicado filtrar as queries por base de dados no Sql Profiler ...

Como é que se revolve o "problema"? Simples, basta forçar o atributo MultipleActiveResultSets na connection string a false.

Parece que foi uma decisão de desenho sempre que a conection string tem o MultipleActiveResultSets a true todas as queries que passam por essa conexão usam a base de dados master.

Fonte: SQL Server Profiler showing EF queries against master database?