The transaction log in a database is an integral part of an SQL Server. Every database has a transaction log, including FogBugz. In Fogbugz, the transaction log is stored within the log file, which is separate from the data file. A transaction log records all the database modifications that you make throughout the entire life of the (FogBugz) database.
While having a decent amount of log is highly recommended, these transaction log files can grow beyond a reasonable size unless they are maintained correctly. This article provides information on how to Shrink such transaction logs to their minimum size, which in turn reclaims disk space.
- Launch SQL Server Management Studio.
- Right-click on the database and choose New Query to open up a query window that is associated with the database with the large transaction log.
- Get the Logical Name of the transaction log file.
- Right-click on the database and select Properties.
- In the Files screen, grab the Logical Name.
The logical name usually ends with
- Execute the following command by substituting the necessary parts with the appropriate logical name and the database name.
Do not use quotes.
DBCC SHRINKFILE(<log_file_name_Log>) BACKUP LOG <database> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<log_file_name_Log>)
- Perform a full backup of the database.
Please refer to the following video for a quick screencast that demonstrates how to change the recovery model for your database and shrink the database logs using SQL Server Management Studio.
The file should shrink to a small shadow of its previous version.
Please see below a little more information from one of our customers.
The ‘proper’ thing to do these days is to put the database into ‘simple recovery’ and then to shrink the log. A few commands that I think might do it:ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE DBCC SHRINKFILE(<log_file_name_Log>) ALTER DATABASE [mydatabase] SET RECOVERY FULL
Some notes on SQL Server backups:
- Transaction logs have to be backed up in order for them to truncate. If they are not backed up regularly, they will eventually fill the disk.
- A full backup alone will not do the trick – that does not touch the transaction log.
- Transaction logs must be backed up via BACKUP LOG.
- If the DBA does not require transaction log backups, it is advisable to move to Simple Recovery
Please find more details in this Microsoft article on Transaction Log Truncation.