Issue
Security Expert database size increasing rapidly and getting large.
From the screen capture below, it can be seen that the Security Expert transaction log file SecurityExpert.ldf has increased to a large size.
Product Line
EcoStruxure Security Expert
Environment
- Security Expert
- Microsoft SQL Server
Cause
The Recovery model of the Security Expert database is set to FULL and the database has not been maintained. When using the Full recovery model, regular backups of the transaction log should be performed to prevent the transaction log file size from growing out of proportion to the database size.
New user databases are created with the same recovery model as the model database. The default recovery model for the model database depends on the edition of SQL Server you have installed. Editions targeted at test and development environments (SQL Server Express) set the recovery model of the model database to SIMPLE. Other editions set the recovery model of the model database to FULL.
Resolution
For Security Expert, it is recommended that the Recovery model be set to the default of SIMPLE.
You can reduce the size of the logs and also prevent them from getting too large again by changing the recovery model of the database from FULL to SIMPLE. The databases are then shrunk to free up drive space.
Refer to the following steps:
- Take a backup of both the SecurityExpert and SecurityExpertEvents databases and store them in a safe location
- Change the Recovery Model of the SecurityExpert database from Full to Simple in SQL Server Management Studio. To do this, right click on the SecurityExpert database and go to its properties. Select 'Options'. Change the Recovery Model setting to Simple.
Recovery Model Simple
- Right click on the SecurityExpert database and select Tasks > Shrink > Files
Shrink
- In the Shrink File - Security Expert window, change the File type to Log.
Shrink File Type Log
- Click OK
- Repeat steps 2-5 for the SecurityExpertEvents database
It is recommended that regular scheduled backups be configured when using the SIMPLE recovery model. In SIMPLE recovery mode, changes since the most recent backup are unprotected.
