Início / dba / Perguntas / 168254
Accepted
Mark Freeman
Asked: 2017-03-28 04:54:50 +0800 CST

Como evitar deadlocks do histórico de trabalhos do SQL Server

  • 772

Das cerca de 50 instâncias que gerencio atualmente, tenho duas que têm um deadlock quase todas as noites envolvendo "INSERT INTO msdb.dbo.sysjobhistory" (sempre a vítima) e "DELETE FROM msdb.dbo.sysjobhistory" chamado por "EXEC msdb.dbo.sp_purge_jobhistory", ambas as sessões executadas pela conta de serviço do SQL Server Agent. Eu nunca tive esse impasse em nenhuma das outras instâncias, nem em nenhum trabalho anterior. Em cada um desses servidores, os deadlocks ocorrem em horários diferentes durante a noite (1:30 sábados executados por um agendamento semanal para sp_purge_jobhistory ou 2:01 executados por uma etapa no agendamento noturno para syspolicy_purge_history).

Não parece haver nada significativamente diferente entre essas duas instâncias de "problema" e qualquer uma das outras. Um é um servidor de teste 2012 SP3 CU3 e o outro é uma instância de produção 2012 SP2.

Alguma ideia de como posso evitar isso?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Eu escrevi isso (uma versão muito simplificada do código na postagem do blog do @Taiob que ele vinculou) e, em seguida, chamei-o de um trabalho em vez do sp_purge_jobhistory fornecido pela Microsoft:

    --  Create Procedure PurgeJobHistory.sql

USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (SELECT 1 FROM sys.procedures p JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.name = N'dbo' AND p.name = N'PurgeJobHistory') BEGIN
   EXEC('CREATE PROCEDURE [dbo].[PurgeJobHistory] AS BEGIN SET NOCOUNT ON; END');
END;
GO

ALTER PROCEDURE [dbo].[PurgeJobHistory]
    @numberOfDaysToKeepSucceeded    SMALLINT    =   7
    ,@numberOfDaysToKeepOther       SMALLINT    =   30
AS
/*
Purpose:        Purges old rows from msdb.dbo.sysjobhistory, providing for different retention for success vs. other statuses
Author:         Mark Freeman (@m60freeman)
Last Edited:    2017-03-29
Compatibility:  SQL Server 2008 and newer
Adapted from:   http://sqlworldwide.com/manage-sql-agent-job-history/
License:        This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                    http://creativecommons.org/licenses/by-nc-sa/3.0/
*/
SET NOCOUNT ON;  
SET DEADLOCK_PRIORITY LOW;

DECLARE @LastDateToRetainSucceeded  INT
        ,@LastDateToRetainOther     INT
        ,@RowsDeleted               INT
        ,@RowCount                  INT;

--Get the actual date of the most recent rows that we wish to maintain (converted match sysjobhistory.run_date)
SELECT  @LastDateToRetainSucceeded  = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepSucceeded), 112))
        ,@LastDateToRetainOther     = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepOther), 112))
        ,@RowCount                  = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Starting rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;

--DELETE sysjobhistory rows 1,000 at a time in a loop until we have nothing left to delete
SET @RowsDeleted = -1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with a Succeeded status
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  =   1                           --Succeeded
           AND  run_date    <   @LastDateToRetainSucceeded;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Succeeded rows deleted: %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SET @RowsDeleted = 1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with all other status values
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  IN  (0, 2, 3)                   --Failed, Retry, and Canceled are all treated as failures.
           AND  run_date    <   @LastDateToRetainOther;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Other rows deleted:     %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SELECT  @RowCount   = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Ending rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;
GO

    Isso nos reduziu de ter mais de 1 milhão de linhas no msdb.dbo.sysjobhistory para apenas cerca de 1/4 disso, e as exclusões não estão mais envolvidas em deadlocks.

    Obrigado a @Taiob e @ScottHodgin por suas contribuições!

    • 0

relate perguntas