Depending on the SQL Server configuration, SQL Server database log files (*.ldf) for Meridian vaults can become overly large (up to 100 GB) and affect performance. The cause is that the SQL Server recovery mode is set to Full (default). The log file size can be reduced dramatically by setting the recovery mode to Simple. Consult with the database administrator in your organization before attempting any of the following changes.
Description
To truncate the existing log file and optimize performance:
Note The following scripts are not compatible with all versions of SQL Server (for example, SQL Server 2008 R2). Refer to the documentation for your version of SQL Server for the correct syntax.
- Perform a Prepare For Backup for every SQL Server vault.
- In SQL Server Management Studio, start a new query.
- Paste the following script into the editor.
- Replace each occurrence of <DATABASE_NAME> in the script with the actual database name.
BACKUP LOG <DATABASE_NAME> WITH TRUNCATE_ONLYDBCC SHRINKDATABASE (<DATABASE_NAME>, TRUNCATEONLY)
- Execute the script. This compacts the log file.
To query the current recovery mode:
- Paste the following script into the editor.
- Replace each occurrence of <DATABASE_NAME> in the script with the actual database name.
SELECT DATABASEPROPERTY ('<DATABASE_NAME>', 'IsTruncLog')
SELECT DATABASEPROPERTYEX('<DATABASE_NAME>', 'Recovery')
- Execute the script. The current recovery mode is returned.
To change the recovery mode to Simple and prevent oversize log files in the future:
- Paste the following script into the editor.
- Replace each occurrence of <DATABASE_NAME> in the script with the actual database name.
ALTER DATABASE <DATABASE_NAME> SET RECOVERY SIMPLE
- Execute the script. The current recovery mode is returned.
If the log files continue to grow after implementing these changes, consult the database administrator in your organization about other SQL Server settings that can be modified.
