Reduce impact on SQL logs

Question

Sharepoint and SQL Server issue as below

When we change setting on SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30

we basically tell SharePoint to purge all data from Search_LinksStore that is older than 30 days.

What it actually does, it starts (after we start respective SPTimerJobs) stored procedure UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup and passes number of days as a parameter, here is manual start example:

EXEC UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup 30;
GO

When this stored procedure runs, it goes trough the tables and performs SELECT and DELETE, but due to enormous number of rows even small reduction on DB has huge impact on sql transaction logs (they generate 200+GB for only 10GB od DB reduction).

In this case, it tried to delete older than 30days but it always hangs due to low disk space on log partition (i even expanded to 400GB ...) and it cannot be done this way.

I will need help of SQL guys and any of SharePoint guys.

Is it possible to create another stored procedure (or modify this one) to have chunks of delete in smaller amount of rows, that way we can reduce impact on sql logs (but it will be performed longer, but i think we can sacrifice time for the sake of log size)?

    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

Please, try to analyze your backup strategies.

Also, You can analyze your DATABASE MODE (FULL, Bulk-Logged, Simple) (https://msdn.microsoft.com/en-us/library/ms189275.aspx)

Also, you can check log space with:

DBCC SQLPERF(LOGSPACE);  
GO  

check this: https://msdn.microsoft.com/en-us/library/ms190674.aspx