Reducing the size of the Log Database

Article Number: 000001267
1267
Products: V-Series, Web Security and Filter, Web Security Gateway, Web Security Gateway Anywhere, Websense Enterprise
Versions: 6.3, 7.0, 7.1, 7.5, 7.6, 7.7

Problem Description

My Websense Log Database has grown too large. I want to make sure that the size of the Log Database stays manageable. What can I do to shrink or control the size of the Log Database?
 
Or,
  
I am running out of disk space on my SQL Server. How do I reduce the transaction log size and/or shrink the Websense Log Databases?
 

Resolution

The Log Database stores the records of Internet activity and the associated Websense filtering actions. Installation creates the Log Database with a catalog database and database partition.

  • The catalog database provides a single connection point for the various Websense components that need to access the Log Database: status pages, Log Server, presentation reports, and investigative reports. It contains supporting information for the database partitions, including the list of category names, risk class definitions, the mapping of users to groups, database jobs, and so forth. The catalog database also maintains a list of all the available database partitions.
  • Database partitions store the individual log records of Internet activity. Users can configure the Log Database to start a new logging partition based on partition size or a date interval.
NOTE:  In addition to the standard logging partition referenced above, V7.7 includes a second type of database partition. The threats partition stores information about requests that have been assigned a severity level. Information from the threats partition is used to populate the v7.7 Threats Dashboard.

There are a number of steps that you can take to help manage the size of the Log Database.

Manage Log Database growth by configuring log record size

You can use the Log Server Configuration Utility to enable options that reduce the size of the records stored in the Log Database. Keep in mind that enabling these options does reduce the precision of the data stored.

  1. Open the Log Server Configuration Utility (Start > Programs > Websense > Utilities > Log Server Configuration) and select the Settings tab.

NOTE:  For v7.7, the settings to enable visits or consolidation are found in Websense Manager on the Settings > Reporting > Log Server page. 

  1. Under Visits Processing, make sure that Enable visits is marked. Enabling this option means that when a user accesses a Web site, a single log record is created, combining all data items on the page (such as graphics and advertisements).
  2. Select the Consolidation tab and then mark the Consolidate Log Records check box. This combines into one record multiple requests by the same user for the same domain to which the same action (permit, block, etc.) is applied.
  3. Click Apply to enable the change.
  4. Click OK to exit the utility.
  5. Open Websense Manager and go to the Settings > Reporting > Log Database page.
  •  NOTE: For v6.3.x:
    1. On the Log Server machine, go to Start > Programs > Websense > Web Reporting Tools.
    2. Log in and click on Database Administrator.
  1. Under Full URL Logging, make sure that the Record full URL of each site requested check box is not selected. This means that the domain name for a requested URL (such as www.websense.com) is recorded in the database, but the path to an individual page (for example, www.websense.com/content/support.aspx) is not saved.
  2. Click Save Now to implement the change.

Manage Log Database size by configuring partition rollover

You can configure the size of the database used in reporting by configuring Websense software to create a new database partition when the database reaches a certain size, or after a certain period of time has passed. (MSDE (pre-7.6) and Sql Exress (7.6 and later) users are offered rollover options based on size only.)  You can also control the amount of space used by current and past database partitions by configuring Websense software to delete older partitions after a specific period of time.

  1. Open Websense Manager and go to the Settings > Reporting > Log Database page.
  2. Under Database Rollover Options, specify what conditions will cause Websense software to create a new database partition (size or time period). For example, you might configure Websense software to create a new partition when the current partition reaches 10,000 MB in size, or when 4 weeks have passed. When a new database partition is created, Websense software automatically configures reporting tools to use the new partition.  
     

    NOTE

    If the rollover begins during a busy part of the day, performance may slow during the rollover process. To avoid this possibility, some environments choose to set the automatic rollover to a long time period or large maximum size. Then, they perform regular manual rollovers to prevent the automatic rollover from occurring.

     
  3. Click Save Now to implement the change.
  4. Under Maintenance Configuration, you can specify that older database partitions be deleted after a certain period of time. Mark the Automatically delete partitions... check box, and then specify how old (in days) the newest record in the partition must be to trigger the deletion. The default value is 365 days.
  5. Click Save Now to implement the change.


How to reduce size of the Websense Log Database

At times, you may see the transaction logs (.ldf) grow unexpectedly, filling up a server's hard drive and resulting in an out of disk space error:

  • Error:  9002, Severity: 17, State: 2 
    The log file for database '%. *ls' is full.

You can decrease the average size of the transaction logs (.ldf files) by changing them from Full Recovery mode to Simple Recovery mode:

  1. Open the Microsoft SQL Server Enterprise Manager utility.
  2. Expand the directory structure and select the database name.
  3. Right-click on the database name and select Properties.
  4. Select the Options tab.
  5. Highlight the drop down box under Recovery model and select Simple.
  6. Select OK and the Properties box will close. 
     

     

Simple Recovery Model

 
 
After Simple Recovery mode has been selected, shrink the database:

  1. Right-click on the database name, and select New Tasks > Shrink Database.
  2. Select OK from the following window.
  3. A pop-up displays: The database has been shrunk successfully.
  4. Select OK on the pop-up.


Simple Recovery 2
 
 
 
 
To ensure all future databases (.ldf files) are created in simple recovery mode, change the recovery mode of system MODEL Database to simple.

Notes & Warnings

Websense Manager Help (Reporting Administration topic)

Moving Database Files from One Partition to Another

  • For version 7.6, the default directory for 'new' installs is C:\Program Files\Websense\Web Security\ (32-bit machines) or C:\Program Files (x86)\Websense\Web Security\ (64 bit machines). The directory for upgrades to v7.6 is C:\Program Files\Websense\.
  • If you see the actual database partition size on the SQL server is larger than the partition rollover set in the TRITON Web Security Log database settings than enable auto re-indexing in the log database settings.  
  • The above procedures can be done to shrink the tempdb if it is consuming a lot of disk space. 
     
  • Possible error when the catalog database log file (.ldf) is full:
    • '[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'wslogdb70' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (SQL-42000)

Article Rating:

Do you have any additional feedback?    close

How are we doing?

Provide us feedback on your experience with the Service Request portal.

provide feedback >