MS-SQL clearing logs

Database: ms SQL Server 2005

Task: ms SQL Server 2005 database log files are too large and need to be cleared.

Method:

Backup log [your database name] With no_log
Backup log [your database name] With truncate_only
DBCC shrinkdatabase ([your database name])

Note:

Backup log specifies that only transaction logs are backed up. This log is backed up from the last successfully executed log to the end of the current log. After the log is backed up, the space that is no longer needed by transaction replication or active transactions may be truncated.

No_log | truncate_only

By dropping all logs other than activity logs, you can delete the inactive logs without backing up and copying the logs, and truncate the logs. This option releases space. Because log backup is not saved, you do not need to specify a backup device. No_log and truncate_only are synonymous.

Here is a question: What is activity log? Microsoft's explanation is:

The part in the log file between the first log record that must exist to ensure successful rollback within the database range and the last written log record is called the activity part of the log, that is, the activity log ". This is the log Part required for full database recovery. Never truncate any part of the activity log.

DBCC shrinkdatabase compresses all data and log files of a specific database. This is the simplest method and can be used.

However, there is a problem:

In the case of low data security requirements or high backup frequency, it is better to use the simple recovery mode to directly cut logs. Microsoft said: In simple recovery mode, transaction logs are automatically truncated after each data backup, that is, non-active logs are deleted. Due to frequent log truncation, there is no transaction log backup. This simplifies backup and restoration. However, without a transaction log backup, it is impossible to restore to the time point of failure.

That is to say, if you do not use logs to recover data (I will not use it myself, and I use Redgate for regular high-frequency backup), the simple mode is the best solution.