Full Backup Type

When training SQL Server, people always have the question: "What should I do when transaction logs grow to a certain extent ?" I usually use another question to answer: "Have you backed up ?" People often reply to this question: "Of course, we back up the entire database every night ." But have you backed up the logs? At this point, the trainees thought that if they could solve this problem, this training would be worthwhile.

When you back up transaction logs, not only the latest committed transactions are backed up, but also the corresponding log files are truncated. Truncation means that the backed-up transaction will be removed from the log file, freeing up space for the new transaction. In fact, if you do not back up transaction logs, it will grow without limit until your hard disk space is insufficient. Cainiao SQL Server DBA always thinks that full backup will truncate transaction logs, but it does not. It will take a snapshot of the transaction log at the end, so that the transaction will be backed up when the full backup is performed, and the log will not be truncated.

Among the students I saw, some people only had 100 MB of database, and the transaction log actually reached 16 GB. What a surprise! The most effective solution is to frequently back up transaction logs. This ensures that your transaction logs are concise. But the problem arises again. To what extent is the frequency? This depends on the situation. Generally, we control the transaction log to half the size of the entire data file. If this limit is exceeded, we need to increase the backup frequency. Sometimes, for some very active databases, we need to back up every 15 minutes.

During the discussion, we first assume that the backup mode is "Full backup", which is recommended for production databases. This mode allows you to back up transaction logs. Therefore, you only need to restore the logs in order before restoring the database to the fault point. The "simple backup" Mode periodically truncates transaction logs and does not support log backup. Therefore, you can only use the last database backup for restoration. It cannot be recovered to the fault point and may cause transaction loss. This mode applies only to test databases or read-only databases.