3

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
  • 1
    I always try and avoid changing stored procedures that ship with the product. One work around that you may consider is to walk the days of cleanup from what it is currently to the desired 30, one day at a time. Subsequently to getting caught up, set it to run every day. This leaves the stored procedure as originally written (so supported) while limiting impact on the log. – Jonathan Fite Dec 29 '15 at 14:43
  • I run it every day. And it delete everything older than 30 days. But it goes through 30 days of data. – Darko Milic Dec 29 '15 at 14:49
  • 1
    I am not sure but by changing the procedure you loose sharepoint support. Please confirm on this before changing. – Shanky Dec 29 '15 at 14:51
  • Because of that , I asked is it maybe possible to create new one which can be started manually. "Is it possible to create another stored procedure... " Only copied procedure which can be used (or some parts). – Darko Milic Dec 29 '15 at 14:59
  • oh, I see. I'll delete my comment. Maybe go the other way? Start at the number of days old the oldest data is, then reduce the number till you get to 30? – Max Vernon Dec 29 '15 at 15:14
-2

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

| improve this answer | |
  • 1
    Martin - Some more detail than simply 'check this' would improve your response. But the links are also fairly generic. – RLF Jun 28 '16 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.