Enterprise | Sql Server 2012

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); sql server 2012 enterprise

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 + '

-- Update final status UPDATE dbo.IndexMaintenanceLog SET EndTime = GETDATE(), Status = 'Completed', CompressionUsed = @CompressionType WHERE TableName = @SchemaName + '.' + @TableName AND Status = 'Running'; END; GO -- Maintenance log table CREATE TABLE dbo.IndexMaintenanceLog ( LogID INT IDENTITY(1,1) PRIMARY KEY, TableName NVARCHAR(256), Action NVARCHAR(100), StartTime DATETIME, EndTime DATETIME, PartitionsProcessed INT DEFAULT 0, LastProcessedTime DATETIME, Status NVARCHAR(50), CompressionUsed NVARCHAR(10), CONSTRAINT CHK_Compression CHECK (CompressionUsed IN ('PAGE', 'ROW', 'NONE')) ); -- Error logging (Enterprise: uses compression) CREATE TABLE dbo.ErrorLog ( ErrorID INT IDENTITY(1,1) PRIMARY KEY, ProcedureName NVARCHAR(256), ErrorMessage NVARCHAR(MAX), ErrorDate DATETIME DEFAULT GETDATE() ); CONSTRAINT PK_SalesFact PRIMARY KEY (SaleID

EXEC dbo.sp_add_schedule @schedule_name = N'Weekly_Sunday_2AM', @freq_type = 8, @freq_interval = 1, @active_start_time = 20000;

-- Log start INSERT INTO dbo.IndexMaintenanceLog (TableName, Action, StartTime, Status) VALUES (@SchemaName + '.' + @TableName, 'Enterprise Optimize', GETDATE(), 'Running');