Partition Database with Partition Manager

Partition Manager (AVEVAWorkTasksPartitionManager.exe) is a Windows application available in the installation path. This application allows you to enable partition on an existing non-partitioned SQL Server data source, and to move data to the secondary database partition.

We recommend enabling or disabling partition only when no users are logged on to the system. That is perform partition activities during a scheduled maintenance lock out period. Also, ensure to restart the system after performing partition activities.

Enable Partition

For an existing data source, partition can be enabled from the Partition Manager tool as follows:

1. Select an existing data source.

2. Enter appropriate path for Database Secondary Data File Path.

3. Click Enable Table Partitioning.

4. Click Yes to move data automatically or click No to move data later manually.

For more information on Database Secondary Data File Path, see Database Secondary Data File Path.

Move Data

For an existing data source, after partition is enabled, data can be moved using the AVEVAWorkTasksPartitionManager.exe as follows:

5. Select an existing data source.

6. Right-click on the data source, and then click Move Data.

Note: Moving data to the secondary database partition consumes time and resources on the server. Therefore, ensure you move data only when there is no load on the server.

Disable Partition

For an existing data source, after partition is enabled, the partition can be removed using the AVEVAWorkTasksPartitionManager.exe as follows:

7. Select an existing data source.

8. Click Disable Table Partitioning.

Note:
- Disabling database partition may take some time based on the size of the database.
- Disabling removes the archived file from SQL Server, but by default SQL Server does not remove the associated file until a log backup is performed. Therefore, ensure to perform a transaction log backup.

If AVEVAWorkTasksPartitionManager.exe fails to disable the partition, then follow these steps:

9. Open Microsoft SQL Server Management Studio.

10. Create a new query for the data source.

11. Run [RemoveSkeltaPartition] stored procedure using Exec [RemoveSkeltaPartition]SQL script.

12. Use the Farm database and update the IsPartitioned column of the SKDataSource table. Use the following SQL query by providing Data source name.

    Update [SKDataSource]

    set IsPartitioned = 0

    Where Name = '< Data Source Name>'

13. Check the ARCHIVED files and ARCHIVED scheme of Files and Filegroups under Database Properties to verify that the partition is completely removed. The system should not show the ARCHIVED file and ARCHIVED scheme under Filegroups.

14. If the above items are shown, manually delete them using SSMS:

    • Select [<Database Name>_ARC.NDF] from Files and then click Remove.

    • Select ARCHIVED scheme from the Filegroups and then click Remove.

    OR

    Execute the below script:

    ALTER DATABASE [<Database Name>] MODIFY FILEGROUP [PRIMARY] DEFAULT

    ALTER DATABASE [<Database Name>] REMOVE FILE [<Database Name>_ARC.NDF]

    ALTER DATABASE [<Database Name>] REMOVE FILEGROUP ARCHIVED

15. Take the transaction log backup.