GetDatabaseFileInfo SP is executing frequently on all EV Databases and causing SQL Performance issues
Article ID: 100064365
Updated On:
Description
Error Message
No error messages are logged in the EV Events or in the DTrace. The SQL trace shows GetDatabaseFileInfo execution attempts after the interval of 4 hours from each EV Server in the environment.
Cause
Enterprise vault provides EV SQL Database file level performance counters and these are available on each EV Server in the environment. These are:
Enterprise Vault Database Files\Hours since last backup
Enterprise Vault Database Files\Database file location % used
It has the counters specified for each database file for each EV Database. These counters are updated by EV by running the GetDatabaseFileInfo Stored Procedure. The interval to run this SP from each EV Server is every 4 hours, starting from the EV Storage Service startup time.
This SP will be executed twice for each database file and hence, depending on the number of database files associated with the particular database, the number of executions will be recorded in the SQL trace.
For e.g. If a fingerprint database has 52 files associated with it, you will see GetDatabaseFileInfo SP is being executed on the fingerprint database 104 times by every EV server after the interval of 4 hours.
The implementation of the GetDatabaseFileInfo SP primarily queries [msdb].dbo.[backupset] table to get the backup information of the specified database and log file. If there are a higher number of entries in this table, the amount of time it takes to run will increase.
Resolution
Generally, an execution plan should provide more details on what exactly is causing the delay and resource utilization while executing this stored procedure. A solution can be implemented based on its results.
One of the causes identified was too many entries in the [msdb].dbo.[backupset] table and it was resolved after creating the non-clustered index on [database_name] and [type] columns.
[msdb] is a MS-SQL system database and careful consideration will be needed before performing any modifications. Veritas recommends you to contact your SQL DBA or Microsoft SQL Support in order to troubleshoot and fix this issue.
Currently, there are no configurable settings available to alter or disable this behavior.
Issue/Introduction
Additional Information
