Got into this issue recently, where Edgesight’s Transaction log bloats rapidly and fills up the whole Drive.
When the TLog is full, you will get the below error when you login to Edgesight Console.
“The transaction log for database ‘ESSDB’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases”
When checked the database, it was set to Full Recovery model. Citrix recommends to keep the database in Simple Mode.
EdgeSight tends to consume large amounts of transaction logs during its grooming job (default time is daily at 00:05) and it will Re-index many of its data tables, thus consuming large amounts of transaction log in terms of disk space. This typically ends up with about 100% of the database size.
Difference between Simple and Full Recovery Model:
The “Full” recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file. This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason. So when the database is set to the “Full” recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.
The “Simple” recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server. With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup). With this recovery model you are exposed to any failures since the last backup completed.
The “Simple” recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the “Full” recovery model.
srinisworld.me 