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

Article:TECH35746  |  Created: 2004-01-25  |  Updated: 2013-08-19  |  Article URL http://www.symantec.com/docs/TECH35746
Article Type
Technical Solution


Issue



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


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
 
Using SQL Server 2000
2.  Click 'Start'| 'Programs' | 'Microsoft SQL Server' | 'Query Analyzer' 
3.  In the left-hand pane, click the 'EnterpriseVaultAudit' database as displayed in Figure 1.
Figure 1:
  

4.  Enter one of the following queries as displayed in Figure 2 and 3.
 Figure 2:
  

 Figure 3:
  
 
SQL Query Function
Delete * from AuditTrail Delete removes records from the table one at a time and records each deletion in the transaction log. Delete is much slower than Truncate.
Truncate table AuditTrail Truncate requires less transaction log space and it does not record each deletion. Truncate is much faster than Delete.

    5.  Click 'Query' then click 'Execute'
 
Using SQL Server 2005/2008
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 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'
 
Query 3 - This will delete ALL entries from the AuditTrail table/Audit Database but is faster than the delete query above
Truncate Table AuditTrail
 
 
 
Creating a new database whilst keeping the existing database:
Basic Steps
1.  Create the new database as described in 'Creating the auditing database' section of this technical note
2.  Stop all the Enterprise Vault services
3.  Edit the properties of the 'EnterpriseVaultAudit' system DSN as described in 'Editing the system DSN' section of this technical note.
4.  Start the Enterprise Vault services
 
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.
 
 
Detailed Steps
1) Creating the auditing database:
Follow the steps below to create the auditing database:

SQL Server 2000
a - Click 'Start' | 'Programs' | 'Microsoft SQL Server' | 'Enterprise Manager'
b - In the left pane of Enterprise Manager, expand the tree to the name of the computer on which the auditing database needs to be created then click the name of that computer.
c - Click 'Action', then 'New', then 'Database'
d - When prompted for the database properties, enter a name for the new database, such as "EnterpriseVaultAudit"
e - Click OK
f - In the left pane of Enterprise Manager, expand the tree until the databases are visible
g - Click the name of the newly created auditing database
h - Click 'Tools' | 'SQL Query Analyzer'
i - In 'SQL Query Analyzer', click 'File' and then 'Open'
j - Select the file 'Audit.sql', which is in the Enterprise Vault program folder (usually C:\Program Files\Enterprise Vault or C:\Program Files (x86)\Enterprise Vault)
k - Click 'Query' and then 'Execute'
 
 
SQL Server 2005/2008
a - Open SQL Server Management Studio and connect to the correct SQL Server
b - Right click 'Databases' in the Explorer view and select 'New Database...' and give the new database a relevant name e.g. EnterpriseVaultAudit2
c - Select the desired paths for the Data and Log files and click 'Ok'
d - Click 'File' | 'Open' | 'File' and browse to the Enterprise Vault installation folder on the EV Server then select the Audit.sql
e - On the SQL Editor toolbar, use the drop down list of Database names to ensure the new Audit Database (EnterpriseVaultAudit2) has been selected
f - Select '! Execute' from the menu option
 
If the operation was successful, the message  '(1 row(s) affected)' will be displayed

2) Editing the 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:
 
a - 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"
b - In 'ODBC Data Source Administrator', click the 'System DSN' tab
c - In the list of System Data Sources, click 'EnterpriseVaultAudit' and then click 'Configure'. The Microsoft SQL Server DSN Configuration wizard starts.
d - On the first page, click 'Next'
e - On the second page, click 'Next'
f - On the third page, under 'Change the default database to', select the database that has just been created
g - Click 'Next'
h - Click 'Finish' to display the summary information
i - Click 'Test Data Source' to verify the connection to the database
j - Click 'OK'
k - 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  -   See Related Articles
 



Legacy ID



273274


Article URL http://www.symantec.com/docs/TECH35746


Terms of use for this information are found in Legal Notices