减少对SQL日志的影响

Question

Sharepoint和Server问题如下

当我们更改SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30上的设置时

我们基本上告诉SharePoint从Search_LinksStore中清除所有超过30天的数据。

实际上，它启动(在启动相应的SPTimerJobs之后)存储过程UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup并将天数作为参数传递，下面是手动启动示例：

EXEC UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup 30;
GO

当这个存储过程运行时，它会在表中执行SELECT和DELETE，但是由于行的数量巨大，即使是DB上的少量减少也会对sql事务日志产生巨大的影响(它们只为10 DB的od减少而生成200+GB )。

在这种情况下，它尝试删除超过30天，但由于日志分区上磁盘空间低，它总是挂起(我甚至扩展到400 In .)这样做是不可能的。

我需要SQL和任何SharePoint人员的帮助。

是否有可能创建另一个存储过程(或修改此存储过程)使删除块以较小的行数进行，这样我们就可以减少对sql日志的影响(但它将被执行得更长，但我认为我们可以为了日志大小而牺牲时间)吗？

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_MSS_QLog_Cleanup](
    @Days int
)
AS
    DECLARE @TooOld datetime
    SET @TooOld = DATEADD(day, -@Days, GETUTCDATE())
    BEGIN TRANSACTION
    DECLARE @OldPageImpressions table (
        pageImpressionId bigint NOT NULL);
    DECLARE @MinId bigint = (SELECT MIN(pageImpressionId) FROM MSSQLogPageImpressionQuery WHERE searchTime >= @TooOld )
    IF (@MinId IS NOT NULL)
    BEGIN
        DELETE FROM 
            MSSQLogPageImpressionQuery
        WHERE
            pageImpressionId < @MinId
        DELETE FROM 
            MSSQLogPageImpressionResult
        WHERE
            pageImpressionId < @MinId
        DELETE FROM 
            MSSQLogPageImpressionBlock
        WHERE
            pageImpressionId < @MinId
        DELETE FROM 
            MSSQLogPageImpressionQueryRule
        WHERE
            pageImpressionId < @MinId
    END
    ELSE
    BEGIN
        TRUNCATE TABLE MSSQLogPageImpressionQuery
        TRUNCATE TABLE MSSQLogPageImpressionBlock
        TRUNCATE TABLE MSSQLogPageImpressionResult
        TRUNCATE TABLE MSSQLogPageImpressionQueryRule
        TRUNCATE TABLE MSSQLogResultDocs
        TRUNCATE TABLE MSSQLogPageClick
        TRUNCATE TABLE MSSQLogO14PageClick
    END
    DELETE FROM 
        MSSQLogPageImpressionQuery
    OUTPUT 
        DELETED.pageImpressionId
    INTO 
        @OldPageImpressions
    WHERE
        searchTime < @TooOld
    DELETE FROM
        dbo.MSSQLogPageImpressionResult
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionBlock
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionQueryRule
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogResultDocs
    WHERE
        lastReference < @TooOld
    DELETE FROM 
        dbo.MSSQLogPageClick
    WHERE
        clickTime < @TooOld
    DELETE FROM 
        MSSQLogO14PageClick
    WHERE
        searchTime < @TooOld
    COMMIT TRANSACTION 

GO

Answer 1

请试着分析你的后备策略。

此外，您还可以分析您的DATABASE MODE (完整的、批量记录的、简单的) (https://msdn.microsoft.com/en-us/library/ms189275.aspx)。

此外，您还可以使用以下方法检查日志空间：

DBCC SQLPERF(LOGSPACE);  
GO  

检查这个：https://msdn.microsoft.com/en-us/library/ms190674.aspx