WHILE @PartitionNumber <= @MaxPartition BEGIN BEGIN TRY -- ONLINE rebuild with compression (Enterprise only) SET @SQL = ' ALTER INDEX ALL ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REBUILD PARTITION = ' + CAST(@PartitionNumber AS NVARCHAR(10)) + ' WITH ( ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = ' + CAST(@MaxDOP AS NVARCHAR(2)) + ', DATA_COMPRESSION = ' + @CompressionType + ', WAIT_AT_LOW_PRIORITY ( MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDurationMinutes AS NVARCHAR(3)) + ' MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) );'; PRINT 'Processing partition ' + CAST(@PartitionNumber AS VARCHAR) + ' of ' + CAST(@MaxPartition AS VARCHAR); EXEC sp_executesql @SQL; -- Update log UPDATE dbo.IndexMaintenanceLog SET PartitionsProcessed = @PartitionNumber, LastProcessedTime = GETDATE() WHERE TableName = @SchemaName + '.' + @TableName AND Status = 'Running'; END TRY BEGIN CATCH -- Log error INSERT INTO dbo.ErrorLog (ProcedureName, ErrorMessage, ErrorDate) VALUES ('Enterprise_OptimizeTablePartitions', ERROR_MESSAGE(), GETDATE()); RAISERROR('Failed on partition %d: %s', 16, 1, @PartitionNumber, ERROR_MESSAGE()); END CATCH; SET @PartitionNumber = @PartitionNumber + 1; END;
SET @ObjectID = OBJECT_ID(@SchemaName + '.' + @TableName);
-- Create large fact table with partitioning CREATE TABLE dbo.SalesFact ( SaleID INT IDENTITY(1,1), SaleDate DATETIME, ProductID INT, CustomerID INT, Amount DECIMAL(18,2), Quantity INT, CONSTRAINT PK_SalesFact PRIMARY KEY (SaleID, SaleDate) ) ON ps_DateRange(SaleDate);
-- Process each partition SET @PartitionNumber = 1;
