To manage database rollover and growth

Steps

  1. Next to Roll over every, indicate how often you want a new partition to be created.
    • For all supported database engines, you can enter a size limit for each partition. When the size limit is reached, a new partition is created.

      The size limit can be set as follows:

      • SQL Server Standard or Enterprise: 100-1,000,000 MB, default 5000
      • Microsoft SQL Server Express: 100-5000 MB, default 3000
    • If you are using Microsoft SQL Server Standard or Enterprise, you can alternatively specify a partition rollover time interval (every 1-52 weeks, or every 1-12 months).
      Note:

      If the rollover begins during a busy part of the day, performance may slow during the rollover process.

      To avoid this, some organizations set the automatic rollover to a long time period or large maximum size. Then, they perform manual rollovers to prevent the automatic rollover from occurring. See Configuring Log Database maintenance options for information about manual rollovers.

      Keep in mind that extremely large individual partitions are not recommended. Reporting performance can slow if data is not divided into multiple, smaller partitions.

    When a new partition database is created, the partition is automatically enabled for use in reporting.

  2. Under Partition Management, provide the following information:
    1. Specify the File Path for creating both the Data and Log files for new database partitions.
    2. Under Init Size set the initial file size for both the Data and Log files that make up new database partitions.
      • SQL Server Standard or Enterprise: Data file initial size 50-500,000 MB, default 2000; Log file initial size 50-250,000 MB, default 100
      • SQL Server Express: Data file initial size 50-5000 MB, default 100; Log file initial size 50-4000 MB, default 100
      Note:

      As a best practice, calculate the average partition size over a period of time, then update the initial size to approximate that value. You might, for example, set the initial size to 80% of the average size. This minimizes the number of times the partition must be expanded, and frees resources to process data into the partitions.

      Use the information in the Growth Rates and Sizing list (below the list of available partitions) for help in making this calculation.

    3. Under Growth, set the increment by which to increase the size of a partition’s Data and Log files when additional space is required.
      • SQL Server Standard or Enterprise: Data file growth 100-500,000 MB, default 500; Log file size 1-250,000 MB, default 100
      • SQL Server Express: Data file growth 1-1000 MB, default 100; Log file size 1-1000 MB, default 100
  3. If you want to create a partition the next time the ETL job runs (see Web protection reporting database jobs), rather than waiting for the next automatic rollover, click Manually Create Partition. This process usually takes a few minutes.
    • To have the new partition use changes made on the Log Database page, click OK and Save and Deploy before clicking Manually Create Partition.
    • Click the Refresh link under the Available Partitions list periodically. The new partition is added to the list when the creation process is complete.
  4. Use the Available Partitions list to review the partitions available for reporting. The list shows the dates covered, as well as the size and name of each partition.

    The number of Available Partitions that can be included in the list is limited to 500. A SQL Server error may occur if you have more than 500 standard logging partitions in your Log Database.

    Mark the check box next to a partition name, and use the buttons below the list to determine whether the partition’s data is used in or excluded from reports, or to delete the partition.

    • Click Enable to include a selected partition’s data in reports. You must enable at least one partition for reporting.

      A maximum of 70 partitions can be enabled on one time.

    • Click Disable to exclude a selected partition’s data from reports.

      Together, the Enable and Disable options allow you to manage how much data is analyzed during report generation and speed report processing.

    • Click Delete to remove a partition that is no longer needed. The partition is actually deleted the next time the nightly database maintenance job runs.

      Only enabled partitions can be deleted. To delete a disabled partition, first enable it, then delete it.

      Warning: Use this option with care. You cannot recover deleted partitions.

    Deleting obsolete partitions minimizes the number of partitions in the Log Database, which improves database and reporting performance. Use this Delete option to delete individual partitions as needed. See Configuring Log Database maintenance options if you prefer to delete older partitions according to a schedule.

  5. Click OK to cache your changes. Changes are not implemented until you click Save and Deploy.