CODEWITHSUNDEEP
sql-servertransaction-log
dana
dana

Reputation: 3

SQL Server Clear transaction log

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

Upvotes: -1

Views: 3007

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89396

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.

Upvotes: 1

Reza Akraminejad
Reza Akraminejad

Reputation: 1499

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

Upvotes: 0

Related Questions