How to perform a rollover or reduce the size of the Enterprise Vault Auditing database

How to perform a rollover or reduce the size of the Enterprise Vault Auditing database

  • Article ID:100044200
  • Last Published:
  • Product(s):Enterprise Vault

Problem

The Enterprise Vault Auditing DB can grow to a very large size and sometimes it is necessary to perform a rollover to a new DB or remove entries from the DB to reclaim some disk space.

This document applies to Enterprise Vault 12.4 and later versions. For instructions about how to perform rollover of the Auditing database in Enterprise Vault 12.3 and earlier see https://www.veritas.com/docs/100016653.

Error Message

 

Cause

 

Solution

The auditing database will grow in size, the amount it grows will depend on how busy the Enterprise Vault system is and the amount of auditing that has been enabled.
Eventually, it may be required to either remove data from the audit database or to create a new database.

Removing data from the database:

Within the 'EnterpriseVaultAudit' database, the 'AuditTrail' table is the only table that will grow significantly.
Remove the contents of the "AuditTrail" table and then continue to use the same database, as follows:

  1. Back up the 'EnterpriseVaultAudit' database
  2. Start SQL Server Management Studio
  3. Right click the EnterpriseVaultAudit (Default DB name) Database and select 'New Query'
  4. In the top right hand window use one of the below queries to either delete from, or truncate, the Audit DB

Query 1  This will delete ALL entries from the AuditTrail table/Audit Database

Delete from AuditTrail

Query 2 - This will also delete ALL entries from the AuditTrail table/Audit Database, but is faster than the delete query above

Truncate Table AuditTrail

Query 3 - This will delete all entries OLDER than midnight on the 31st of November 2012 and leave anything newer in the DB

Delete from AuditTrail where AuditDate < '2012-12-01 00:00:00.000'
 

Creating a new database whilst keeping the existing database:

A. Create the new database.

  1. Open SQL Server Management Studio and connect to the correct SQL Server.

  2. Right click 'Databases' in the Explorer view and select 'New Database...' and give the new database a relevant name e.g. EnterpriseVaultAudit2.

  3. Select the desired paths for the Data and Log files and click 'Ok'.

  4. Click 'File' | 'Open' | 'File' and browse to the Enterprise Vault installation folder on the EV Server then select the Audit.sql.

  5. On the SQL Editor toolbar, use the drop down list of Database names to ensure the new Audit Database (EnterpriseVaultAudit2) has been selected.

  6. Select '! Execute' from the menu option.
    If the operation was successful, the message  '(1 row(s) affected)' will be displayed.

  7. Repeat steps d, e, and f for AuditDBRoles.sql.

B. Stop all the Enterprise Vault services.

C. Edit the properties of the 'EnterpriseVaultAudit' system DSN.

The system DSN must always be called 'EnterpriseVaultAudit' no matter what the name of the Audit Database actually is.
The existing system DSN must be edited so that it connects to the new database.
To edit the system DSN, stop all the Enterprise Vault services and then do the following on each computer that logs Enterprise Vault audit information:

  1. On the Enterprise Vault Server open the  'OBDC Data Source Administrator' For 32bit operating systems, this may be found under Administrative Tools. For 64 bit operating systems, it may be opened with "C:\Windows\SysWOW64\odbcad32.exe"
  2. In ' ODBC Data Source Administrator', click the ' System DSN' tab
  3. In the list of System Data Sources, click 'EnterpriseVaultAudit' and then click ' Configure'. The Microsoft SQL Server DSN Configuration wizard starts.
  4. On the first page, click ' Next'
  5. On the second page, click ' Next'
  6. On the third page, under ' Change the default database to', select the database that has just been created
  7. Click ' Next'
  8. Click ' Finish' to display the summary information
  9. Click ' Test Data Source' to verify the connection to the database
  10. Click ' OK'
  11. The Enterprise Vault Services can now be restarted and all Auditing information will now be logged in the new DB.

If desired, the original Audit DB files may now be moved to a new location to free up disk space.

To view the Audit information held in the original Audit DB, use the AuditViewer to connect to it.

D. Start the Enterprise Vault services.

E. Enable OData access for auditing.

  1. Start the SQL Server Management Studio.
  2. Right click the EnterpriseVaultDirectory Database and select New Query, and run the following SQL query
    USE [EnterpriseVaultDirectory]
    UPDATE [ExtendedSetting]
    SET [settingValueNumeric] = 1
    WHERE [settingTypeId] = 12901

Each computer on which auditing is enabled has a limited number of connections that it can make to the auditing database.  These connections are reused as needed.

Was this content helpful?