Topic: On rare occasions it may be necessary to shrink or truncate the transaction log of an SQL Database if it becomes too large. Most SQL backup software will either automatically truncate the log file, or will offer options to do this automatically. In some cases, it may be necessary to truncate the logs manually. The transaction database is a record of all queries, inserts, deletes and updates that are done against the database. If needed an administrator can update a restored database from the transaction logs.
Product version affected: All SES versions
Environment: Windows Server OS and SQL 2014 Full and SQL Express
Steps to follow:
Note: The scripts in this article are only intended for Microsoft SQL Server 2014.
Warning: Do a complete backup before running the following commands.
In the commands, replace <SESDB> with the name of your SES Database.
1. It is recommended to perform a back up of the Transaction Log (SQL Server) before running the below queries
https://msdn.microsoft.com/en-CA/library/ms179478.aspx
Optional: Customer can consider doing a Full Database Backup (SQL Server)
https://msdn.microsoft.com/en-CA/library/ms187510.aspx
2. Temporarily change the database recovery model to "simple". USE <SESDB> GO
alter database <SESDB> set recovery simple GO
3. Issue a checkpoint.
CHECKPOINT GO
The checkpoint process writes all the dirty pages in the memory to disk. On a simple recovery mode, the checkpoint process clears the inactive portion of the transaction log.
4. Shrink the transaction log.
USE <SESDB> GO dbcc shrinkfile(<SESDB>_Log) Note: There is no space between the database name and _Log.
5. Change the recovery model back to "full/bulk logged".
USE <SESDB> GO alter database <SESDB> set recovery full
GO
After these steps the log file size should be reduced.
Comment: It is unusual for the Log file to grow to an extraordinary size, and my be indicative of less-than-ideal database backup practices, although there have been scenarios in the past where unusually high network loads have triggered SDConnex to generate unusual levels of SQL changes (the risks associated with this possibility have been eliminated from the product in later versions). If you are encountering unusual Transaction Log growth, please contact WinMagic Support to permit them to understand what is happening in your SES environment.