Security Expert database size increasing rapidly and getting large
Security Expert database size:
From the screen capture below, it can be seen that the Security Expert transaction log file SecurityExpert.ldf has increased to a large size.
- Security Expert
- Microsoft SQL Server
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.
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 can be 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 SecruityExpert database and go to its properties. Select 'Options'. You should be able to see Recovery model setting.
- Right click on the SecurityExpert database and select Tasks > Shrink > Files
- In the Shrink File - Security Expert window, change the File type to 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.