Renaming or truncating the transaction log (SQL)

If your database is involved in synchronization or replication, rename the transaction log to create a new transaction log for the database. If your database is not involved in replication and you have limited disk space on your computer, truncate the transaction log.

Prerequisites

You must have BACKUP DATABASE system privilege.

Context and remarks

If your database is involved in synchronization or replication, it is good practice to maintain copies of old transaction logs until you are certain they are no longer needed. Alternatively to renaming the transaction log, you can continue to use the existing transaction log when you back up your database.

If you truncate the transaction log during backup to delete the contents of the online transaction log, to recover your database from media failure on the database file you must use every backup copy made since the last full backup.

Task

Use the BACKUP statement with the following clauses:

Option

Action

Rename the transaction log

BACKUP DATABASE
DIRECTORY backup-directory
[ TRANSACTION LOG ONLY ]
TRANSACTION LOG RENAME;

Truncate the transaction log

BACKUP DATABASE
DIRECTORY backup-directory
[ TRANSACTION LOG ONLY ]
TRANSACTION LOG TRUNCATE;

Include the TRANSACTION LOG ONLY clause only if you are making an incremental backup.

The backup copies of the transaction log and database file are placed in backup-directory. If you provide a path, it is relative to the working directory of the database server, not your client application.

Results

A backup is made and the transaction log is renamed or truncated.

Renaming or truncating the transaction log (SQL)

Task

See also