SQL Server Clear transaction log

Question

I have a database that is in Full Recovery Mode that I restored from a backup file

Now I want to clear/truncate the transaction log file. How I can do this.

Step 1 I tried to backup the transaction log using

BACKUP LOG [test] TO  DISK

but I get error:

BACKUP LOG cannot be performed because there is no current database backup.

So what I should do to clear the transition log if I want to keep the database in Full Recovery Mode

I have no idea why you would want to do this but you can do `ALTER DATABASE [test] SET RECOVERY SIMPLE; ALTER DATABASE [test] SET RECOVERY FULL;` The whole point of using Full recovery is to keep the backup chain intact, don't do this if you want to be able to restore from a new backup — Charlieface, Aug 26 '21 at 18:54

score 1 · Accepted Answer · answered Aug 26 '21 at 20:20

This:

BACKUP LOG cannot be performed because there is no current database backup.

Means your database is in "pseudo-full" recovery, and until someone takes a full backup there's no way or need to take log backups, and the log will be still be truncated after each checkpoint, just like in the SIMPLE recovery model.

score 0 · Answer 2 · answered Aug 26 '21 at 19:59

One solution is using shrink database files from database menus.

Another is using some scripting(change dbname to your database name):

ALTER DATABASE dbname
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (dbname_Log,10);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE dbname
SET RECOVERY FULL;  
GO

SQL Server Clear transaction log

2 Answers2