Секционирование существующих таблиц с журналом Робота и Оркестратора для SQLServer

ALTER DATABASE ltoolslogs
ADD FILEGROUP ltoolslogs_group_20260226;
GO

ALTER DATABASE ltoolslogs
ADD FILE (
    NAME = ltoolslogs_20260226,
    FILENAME = '<file-groups-path>\ltoolslogs_20260226.ndf',
    SIZE = 10 MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024 KB
) TO FILEGROUP ltoolslogs_group_20260226;
GO

CREATE PARTITION FUNCTION LogsPartitionFunction(datetime2(7))
AS RANGE RIGHT
FOR VALUES ('2026-02-26 00:00:00.0000000');
GO

CREATE PARTITION SCHEME LogsPartitionScheme
AS PARTITION LogsPartitionFunction
TO ('Primary', 'ltoolslogs_group_20260226');
GO

ALTER TABLE [Logs]
DROP CONSTRAINT [PK_Logs];
GO

ALTER TABLE [Logs] ADD CONSTRAINT [PK_Logs] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC,
    [OrchTimestampUtc] ASC
)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

CREATE CLUSTERED INDEX IX_Logs_OrchTimestampUtc
ON [Logs] ([OrchTimestampUtc] ASC)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

CREATE NONCLUSTERED INDEX [IX_Logs_EntityId]
ON [Logs] ([EntityId] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_Event]
ON [Logs] ([Event] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_Monitoring]
ON [Logs]
(
    [RobotId] DESC,
    [OrchTimestampUtc] DESC,
    [LogType] ASC,
    [Type] DESC,
    [OperationKey] ASC
)
INCLUDE([RdpUserName]) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_Monitoring_1]
ON [Logs]
(
    [Type] DESC,
    [LogType] ASC,
    [OperationKey] ASC,
    [OrchTimestampUtc] DESC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_Monitoring_2]
ON [Logs]
(
    [OrchTimestampUtc] DESC,
    [RobotId] DESC,
    [LogType] ASC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_Monitoring_3]
ON [Logs]
(
    [LogType] ASC,
    [OrchTimestampUtc] DESC,
    [ProjectId] DESC,
    [OperationKey] ASC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_OperationKey]
ON [Logs] ([OperationKey] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_ProjectId]
ON [Logs] ([ProjectId] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_RobotId]
ON [Logs] ([RobotId] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_RobotId_OperationKey_TimestampUtc]
ON [Logs]
(
    [RobotId] ASC,
    [OperationKey] ASC,
    [TimestampUtc] DESC
)
INCLUDE([Type],[LogType]) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_RobotKey]
ON [Logs] ([RobotKey] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_UserId]
ON [Logs] ([UserId] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_Logs_WorkerId]
ON [Logs] ([WorkerId] ASC) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

ALTER TABLE [OrchEvents]
DROP CONSTRAINT [PK_OrchEvents];
GO

ALTER TABLE [OrchEvents] ADD CONSTRAINT [PK_OrchEvents] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC,
    [OrchTimestampUtc] ASC
)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

CREATE CLUSTERED INDEX IX_OrchEvents_OrchTimestampUtc
ON [OrchEvents] ([OrchTimestampUtc])
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

CREATE NONCLUSTERED INDEX [IX_OrchEvents_AllOrchTimestampUtc]
ON [OrchEvents]
(
    [TenantId] ASC,
    [Event] ASC,
    [UserId] ASC,
    [EventType] ASC,
    [OrchTimestampUtc] DESC
)
INCLUDE([EntityId],[OperationKey],[Signature],[WorkerAdminName],[Text],[IP],[AssignmentId],[LogType])
WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_OrchEvents_EntityId_OrchTimestampUtc]
ON [OrchEvents]
(
    [EntityId] ASC,
    [OrchTimestampUtc] ASC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_OrchEvents_Event_OrchTimestampUtc]
ON [OrchEvents]
(
    [Event] ASC,
    [OrchTimestampUtc] ASC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO
 
CREATE NONCLUSTERED INDEX [IX_OrchEvents_UserId_OrchTimestampUtc]
ON [OrchEvents]
(
    [UserId] ASC,
    [OrchTimestampUtc] ASC
) WITH (DROP_EXISTING = ON)
ON [LogsPartitionScheme]([OrchTimestampUtc]);
GO

CREATE PROCEDURE dbo.create_partition
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
	 -- Ensures all errors, including those with lower severity, cause a transaction rollback
	SET XACT_ABORT ON;
 
	DECLARE @DbName sysname = DB_NAME();
	DECLARE @CurrentDate DATE = GETDATE();
	DECLARE @NewBoundaryDate DATE; -- next day
	DECLARE @BoundaryValue NVARCHAR(24); -- boundary value for split partition function
	DECLARE @Suffix NVARCHAR(10); -- suffix for file/fule group/section
 
	DECLARE @FilegroupName sysname;
	DECLARE @FileName NVARCHAR(511);
	DECLARE @FileNamePath NVARCHAR(511);
	DECLARE @FileExists INT;
 
	DECLARE @functionName AS NVARCHAR(128) = N'LogsPartitionFunction';
	DECLARE @SchemeName AS NVARCHAR(128) = N'LogsPartitionScheme';
 
	-- Optional: Log error details or re-throw the error
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT = 16;
	DECLARE @ErrorState INT = 1;
 
	-- Declare a variable for the dynamic SQL command
	DECLARE @Sql NVARCHAR(MAX);
 
	---- Создание файловой группы и файла с данными и индексами секции
	-- Get next Date and suffix for it
	SET @NewBoundaryDate = DATEADD(DAY, 1, @CurrentDate);
 
	SET @BoundaryValue = CONCAT(YEAR(@NewBoundaryDate), N'-', FORMAT(MONTH(@NewBoundaryDate), '00'), N'-', FORMAT(DAY(@NewBoundaryDate), '00'), N'T00:00:00.000');
	PRINT N'Boundary for next date [' + CAST(@NewBoundaryDate as VARCHAR(20)) + N'] = ' + @BoundaryValue;
 
	SET @Suffix = CONCAT(YEAR(@NewBoundaryDate), FORMAT(MONTH(@NewBoundaryDate), '00'), FORMAT(DAY(@NewBoundaryDate), '00'));
	PRINT N'Suffix for next date [' + CAST(@NewBoundaryDate as VARCHAR(20)) + N'] = ' + @Suffix;
 
	-- Set FileGroupName and FileName for new section
	SET @FileGroupName = CONCAT(N'ltoolslogs_group_', @Suffix);
	SET @FileName = CONCAT(N'ltoolslogs_', @Suffix);
 
	-- Check and add filegroup for current section
	PRINT CHAR(13) + CHAR(10) + N'Check if exists filegroup [' + @FileGroupName + N']...';
	IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = @FileGroupName)
	BEGIN
		PRINT N'Filegroup [' + @FileGroupName + N'] was not found. Creating it.';
		BEGIN TRY
			-- Construct the dynamic SQL statement
			SET @Sql = N'ALTER DATABASE CURRENT ADD FILEGROUP [' + @FileGroupName + N'];';
			-- Print the command for verification (optional)
			PRINT @Sql;
			-- Execute the dynamic SQL command
			EXEC sp_executesql @Sql;
			PRINT N'Filegroup [' + @FileGroupName + N'] was added for database "' + @DbName + '"';
		END TRY
		BEGIN CATCH
			-- Optional: Log error details or re-throw the error
			SET @ErrorMessage = ERROR_MESSAGE();
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();
			THROW @ErrorMessage, @ErrorSeverity, @ErrorState;
		END CATCH;
	END
	ELSE
	BEGIN
		PRINT N'Filegroup [' + @FileGroupName + N'] is exists already.';
	END
 
	-- Looking for Current File Name
	SET @FileNamePath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(511)) + @FileName + N'.ndf';
 
	-- Check and add filegroup for current section
	PRINT CHAR(13) + CHAR(10) + N'Check if exists file [' + @FileNamePath + N']...';
 
	-- Execute the extended stored procedure, providing the output variable
	EXEC master.dbo.xp_fileexist @FileNamePath, @FileExists OUTPUT;
 
	-- Check the output parameter in an IF NOT EXISTS structure
	IF (SELECT @FileExists) = 0
	BEGIN
		PRINT N'File [' + @FileNamePath + N'] was not found. Creating it.';
		BEGIN TRY
			-- Construct and execute dynamic SQL to add the file to the filegroup
			SET @Sql = N'ALTER DATABASE CURRENT ADD FILE
			(
				NAME = N''' + @FileName + N''',
				FILENAME = N''' + @FileNamePath + N''',
				SIZE = 10240KB,
				FILEGROWTH = 10240KB
			) TO FILEGROUP [' + @FileGroupName + N'];';
 
			-- Print the command for verification (optional)
			PRINT @Sql;
			EXEC sp_executesql @Sql;
			PRINT N'File [' + @FileNamePath + N'] was created and added to [' + @FileGroupName + N'] filegroup.';
		END TRY
		BEGIN CATCH
			-- Optional: Log error details or re-throw the error
			SET @ErrorMessage = ERROR_MESSAGE();
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();
			THROW @ErrorMessage, @ErrorSeverity, @ErrorState;
		END CATCH;
	END
	ELSE
	BEGIN
		PRINT N'File [' + @FileNamePath + N'] is exists already.';
	END
 
	---- Изменение схемы и функции секционирования
	PRINT CHAR(13) + CHAR(10) + N'Check if exists partition scheme [' + @SchemeName + N'] and function ['+ @functionName + N']...';
 
	-- Check if the partition scheme does not exist
	IF NOT EXISTS (SELECT name FROM sys.partition_schemes WHERE name = @SchemeName)
	BEGIN
		SET @ErrorMessage = N'Partition scheme [' + @SchemeName + N'] does not exist. Throw and skip job.';
		PRINT @ErrorMessage;
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
	END
	ELSE
	-- Check if the partition function does not exist
	IF NOT EXISTS (SELECT name FROM sys.partition_functions WHERE name = @functionName)
	BEGIN
		SET @ErrorMessage = N'Partition function [' + @functionName + N'] does not exist. Throw and skip job.';
		PRINT @ErrorMessage;
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
	END
	ELSE
	BEGIN
		BEGIN TRY
			-- 1. Ensure a filegroup is marked as NEXT USED in the partition scheme
			PRINT N'Altering partition scheme [' + @SchemeName + N']...';
			SET @Sql = N'ALTER PARTITION SCHEME ' + QUOTENAME(@SchemeName) + N' NEXT USED ' + QUOTENAME(@FilegroupName) + N';';
 
			-- Print the command for verification (optional)
			PRINT @Sql;
			EXEC sp_executesql @Sql;
 
			-- 2. Split the partition function at the new boundary value
			PRINT N'Altering partition function [' + @functionName + N']...';
			SET @Sql = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@functionName) + N'() SPLIT RANGE (' + N'''' +  CAST(@BoundaryValue AS VARCHAR(24)) + '''' + N');';
 
			-- Print the command for verification (optional)
			PRINT @Sql;
			EXEC sp_executesql @Sql;
			PRINT N'Partition function [' + @functionName + N'] altered successfully.';
		END TRY
		BEGIN CATCH
			-- Optional: Log error details or re-throw the error
			SET @ErrorMessage = ERROR_MESSAGE();
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();
			THROW @ErrorMessage, @ErrorSeverity, @ErrorState;
		END CATCH;
	END;
END;

EXEC dbo.create_partition;

USE msdb;
GO
 
EXECUTE dbo.sp_add_job @job_name = N'Create partition at ltoolslogs';
GO
 
EXECUTE sp_add_jobstep
    @job_name = N'Create partition at ltoolslogs',
    @step_name = N'Execute procedure create_partition',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.create_partition',
    @database_name = N'ltoolslogs',
    @retry_attempts = 5,
    @retry_interval = 5;
GO
 
EXECUTE dbo.sp_add_schedule
    @schedule_name = N'RunWeekly',
    @freq_type = 8,                -- еженедельное расписание
    @freq_interval = 64,           -- выполнение по субботам
    @freq_subday_type = 1,         -- точное время
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 1,
    @active_start_time = 131000,   -- время начала 13:10:00
    @active_end_time = 233000;     -- время окончания 23:30:00
GO
 
EXECUTE sp_attach_schedule
    @job_name = N'Create partition at ltoolslogs',
    @schedule_name = N'RunWeekly';
GO
 
EXECUTE dbo.sp_add_jobserver @job_name = N'Create partition at ltoolslogs';
GO

DECLARE @p INT = $PARTITION.LogsPartitionFunction('2026-03-03');
TRUNCATE TABLE Logs WITH (PARTITIONS(@p));
TRUNCATE TABLE OrchEvents WITH (PARTITIONS(@p));

SELECT @p AS [partition number], COUNT(*) AS [Logs rows] FROM dbo.Logs
WHERE $PARTITION.LogsPartitionFunction(OrchTimestampUtc) = @p;
 
SELECT @p AS [partition number], COUNT(*) AS [OrchEvents rows] FROM dbo.OrchEvents
WHERE $PARTITION.LogsPartitionFunction(OrchTimestampUtc) = @p;