Configuring database partition options

Web Security Help | Web Security Solutions | Version 7.8.x

Related topics:

Log Database administration settings

Log Database sizing guidance

Configuring Internet browse time options

Configuring how URLs are logged

Configuring Log Database maintenance options

Configuring trend and application data retention

Use the Database Rollover Configuration section of the Settings > Reporting > Log Database page to specify when you want the Log Database to create a new database partition (roll over), where database partitions are stored, and how large partitions are. Also create new partitions manually, rather than waiting for the planned rollover, and review all database partitions available for reporting.

Refer to the Growth Rates and Sizing chart at the bottom of the Database Rollover Configuration section for average daily database partition size over time. This may be helpful in planning for future growth, determining how frequently to create new partitions, and in setting partition size and growth options.

Use the drop-down list under the chart to configure the Time period displayed. (The time period is based on the partition creation date; not the dates that the partition spans.) You can display partitions created in the last 1 week, 1 month, 3 months, 6 months, or show all available partitions.

Note that when a longer time period is selected, each partition may appear as a small dot on the chart.

Indicate whether or not to Show chart legend. When displayed, the legend indicates which partitions (by name) are mapped in the chart.

The legend is only available when the chart includes 20 or fewer partitions for the selected time period.

Select a section of the chart to view it more closely. Click Zoom Out or Reset Chart to reduce the level of detail shown.

For more help with database sizing, see Log Database sizing guidance.

To manage database rollover and growth:

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-8000 MB, default 5000

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 on 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.

Under Partition Management, provide the following information:

Specify the File Path for creating both the Data and Log files for new database partitions.

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.

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

If you want to create a partition the next time the ETL job runs (see 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.

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.

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.

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.

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