Очистка лога SysPrcElHistoryLog (несколько млн записей)

SysPrcElHistoryLog и SysProcessLog - это можно удалять.

Записи журнала процессов доступны в разделе "Журнал процессов". 

Ниже представлена инструкция, которая позволяет очистить таблицы SysPrcElHistoryLog, SysPrcEntityHistory, SysPrcHistoryLog (это таблицы куда переводятся в архив записи из SysProcessLog)

Для очистки таблиц необходимо:

1 - создать процедуру очистки, выполнив скрипт: 

SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysPrcHistoryLogById]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DeleteSysPrcHistoryLogById]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DeleteSysPrcHistoryLogById]
	@SysProcessLogId uniqueidentifier
AS
BEGIN
 
	SET NOCOUNT ON
 
	DECLARE @sysProcessLogRecordsToDelete TABLE (Id uniqueidentifier)
 
	INSERT INTO @sysProcessLogRecordsToDelete ([Id])
		SELECT Id FROM SysPrcHistoryLog
		WHERE ParentId = @SysProcessLogId
	OPTION (OPTIMIZE FOR (@SysProcessLogId UNKNOWN))
 
	DECLARE @COUNT int = (SELECT COUNT(*) FROM @sysProcessLogRecordsToDelete)
	PRINT '-- ChildProcessLogToDeleteCount = ' + CAST(@COUNT AS nvarchar(50))
	WHILE (@COUNT) != 0
		BEGIN
			DECLARE @parentId uniqueidentifier = (SELECT TOP 1 Id from @sysProcessLogRecordsToDelete)
			PRINT '---- ChildProcessLogId = ' + CAST(@parentId AS nvarchar(50))
			DELETE FROM @sysProcessLogRecordsToDelete WHERE Id = @parentId;
			EXEC [dbo].[tsp_DeleteSysPrcHistoryLogById] @parentId
			SET @COUNT = @COUNT - 1
		END
 
	DELETE FROM SysPrcElHistoryLog
	WHERE SysProcessId = @SysProcessLogId
	DELETE FROM SysPrcEntityHistory
	WHERE SysProcessId = @SysProcessLogId
	DELETE FROM SysPrcHistoryLog
	WHERE Id = @SysProcessLogId
 
END
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysPrcHistoryLogByStartDate]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DeleteSysPrcHistoryLogByStartDate]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DeleteSysPrcHistoryLogByStartDate]
	@StartDate date
AS
BEGIN
 
	SET NOCOUNT ON
 
	IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
	BEGIN
		DROP TABLE #SysProcessLogId
	END
 
	CREATE TABLE #SysProcessLogId (Id uniqueidentifier)
 
	INSERT INTO #SysProcessLogId ([Id])
		SELECT Top 1000000 SysPrcHistoryLog.Id FROM SysPrcHistoryLog
		WHERE SysPrcHistoryLog.ParentId IS NULL
		AND SysPrcHistoryLog.StartDate < @StartDate
 
	DECLARE SysProcessLogIdCursor CURSOR STATIC LOCAL FOR
	SELECT
		Id
	FROM 
		#SysProcessLogId
 
	DECLARE @COUNT int = (SELECT COUNT_BIG(*) FROM #SysProcessLogId)
 
	DECLARE @processLogId uniqueidentifier
 
	PRINT 'STATE - OPEN SysProcessLogIdCursor ' + RTRIM(CAST(CONVERT(time, GETDATE()) AS nvarchar(30)))
	OPEN SysProcessLogIdCursor
	WHILE (@COUNT > 0)
		BEGIN
			FETCH NEXT FROM SysProcessLogIdCursor INTO @processLogId
			IF @@FETCH_STATUS = -1 BREAK
			IF @@FETCH_STATUS = -2 CONTINUE
			PRINT 'START DELETE ProcessLogId = ' + CAST(@processLogId AS nvarchar(50)) + ' Time = ' + RTRIM(CAST(CONVERT(time, GETDATE()) AS nvarchar(30)))
			EXEC [dbo].[tsp_DeleteSysPrcHistoryLogById] @processLogId
			PRINT 'FINISH DELETE ProcessLogId = ' + CAST(@processLogId AS nvarchar(50)) + ' Time = ' + RTRIM(CAST(CONVERT(time, GETDATE()) AS nvarchar(30)))
			SET @COUNT = @COUNT - 1
			PRINT 'Count = ' + CAST(@COUNT AS nvarchar(50))
		END
	CLOSE SysProcessLogIdCursor
	DEALLOCATE SysProcessLogIdCursor
	PRINT 'STATE - CLOSE SysProcessLogIdCursor ' + RTRIM(CAST(CONVERT(time, GETDATE()) AS nvarchar(30)))
 
	IF NOT OBJECT_ID('#SysProcessLogId') IS NULL
	BEGIN
		DROP TABLE #SysProcessLogId
	END
 
END
GO

2 - выполнить запрос:

DECLARE @date date = '2017-11-01'
 
exec [dbo].[tsp_DeleteSysPrcHistoryLogByStartDate] @date

далее в запросе '2017-11-01' - это дата до которой будут безвозвратно удалены первые 1000 000 записей из SysPrcHistoryLog, Также, будут удалены связанные записи из таблиц SysPrcElHistoryLog, SysPrcEntityHistory.

У Вас очень старая 7.7, могут быть отличия, запускать осторожно.

А вообще, в 7.15.3 обещают автоматическую чистку таких архивных таблиц, есть смысл обновиться.

спасибо. настрою очистку.   мы уже обновились до 15.3 , и именно после этого начал увеличиваться SysPrcElHistoryLog, на ранних версиях я вообще не обращал внимания на эту таблицу

Владимир, ели у Вас 7.15.3, то лучше не скриптом.

Для тех, кто обновляется, а не ставит с нуля, перенос в эти таблицы из основных таблиц процессов включен, вот и растёт, а чистка на всякий случай выключена. Чтобы включить, достаточно поменять значение системной настройки «Срок хранения архивных данных (дней)» с 0 на натуральное число.

Мотков Илья,

как в переменную date добавить условие, чтобы удалял все старше 30 дней? 

Можно программно отнять от текущей даты 30 дней при помощи DATEADD(DAY, -30, GETDATE()) и передать результат в функцию.