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