[Source] SQL Drip—Solves the problem of large database log files

As the database is used for a long time, the log file is also increasing, and here are several ways to reduce this file.

1. Delete the log file directly

Decoupled databases. Before separating the database, be sure to make a full backup of the database, select the database - right-click - task - detach, as shown in the figure below

Copy and paste the log and data files into a separate folder just in case. Delete the link, as shown below

Delete the log file directly, and then attach the database, as shown in the following figure

When attaching, both the ldf file and the mdf file will be automatically attached, but it will prompt that the ldf file cannot be found, it doesn't matter, select the ldf file line, click the delete button below, and click OK. As shown below

The new ldf file generated after the success of Home Inn is only 504K.

2. Shrink log files

You can also shrink the log file to reduce the log file size. Right-click Database-Task-Shrink-File and select the log file, as shown in the figure below

In the shrink operation option, you can select the second item, "Reorganize the page after freeing up unused space money", and then specify a size, click OK.

3. Use the failback model as a simple model

To prevent the log file from growing in the future, you can also use the failback model as a simple model. Right-click Database-Properties-Options-Recovery Mode to select it simply. As shown below

You can also use statements to achieve this function.

SQL statement: alter database database name set recovery simple Also, Truncate log on checkpoint (this option is used in SQL 7.0, SQL 2000 i.e. the failback model is selected as the simple model) When executing the CHECKPOINT command, if the transaction log file exceeds 70% of its size, its contents are cleared, and this option is often set to True Auto when developing the database Shrink periodically checks the database, when the unused space of a database file or log file exceeds 25% of its size, the system will automatically shrink the file so that the unused space is equal to 25%, and when the file size does not exceed the initial size when it was created, the reduced file must also be greater than or equal to its initial size, and the reduction of transaction log files can only be done when it is backed up or when the Truncate log on checkpoint option is set to True.

Note: The default attributes of the database are generally set, but the database attributes are changed due to unexpected circumstances, please check the above attributes of the database after clearing the log to prevent the transaction log from filling up again.