Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.
Issue
Seeing multiple losses of functionality starting to occur, such as controllers going offline, browsing with the client being slow, reports failing to run and events not being logged.
Product Line
EcoStruxure Security Expert
Environment
- Security Expert
- SQL Server
Cause
In SQL Server the SecurityExpertEvents database has a very large LOG file ( hundreds of gigabytes) resulting in degraded performance in SQL and Security Expert.
The Recovery model of the SecurityExpertEvents 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
Reduce the size of the log file and prevent it 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 SecurityExpertEvents database from Full to Simple in SQL Server Management Studio. To do this, right-click on the SecurityExpertEvents database and go to its properties. Select 'Options'. Change the Recovery Model setting to Simple.
Recovery model simple
- Right-click on the SecurityExpertEvents database and select Tasks > Shrink > Files
Shrink files
- In the Shrink File - SecurityExpertEvents window, change the File type to Log.
Shrink log file
- Click OK
- Repeat steps 2-5 for the SecurityExpert 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.
