July 26, 2012 at 12:20 pm
We are running SQL Server 2008 R2 SP1. I attempted to truncate our transaction log by running the following:
BACKUP LOG dbname WITH TRUNCATE_ONLY;
GO
This command has worked in the the past with SQL Server 2005. I do not need the actual log backed up, we are not backing up logs, (yes, we are in full recovery mode), I just want to truncate it.) But, with SQL Server 2008, I am getting the following error:
Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.
What is the new option in SQL Server 2008?
I thought I read "the default behavior for a transaction log backup is to truncate the log." If this is the case would I just execute:
BACKUP LOG dbname;
GO
Thanks, Kevin
July 26, 2012 at 12:24 pm
If you aren't backing up the t-log, then why is the database using the full recovery model? You could just as easily use the simple recovery model.
The way you truncate the t-log now is to switch the database to the simple recovery model, then back to full recovery model. After switching back, you want to run at least a differential backup in case you decide to run a t-log backup later.
July 26, 2012 at 12:41 pm
kevinsql7 (7/26/2012)
Msg 155, Level 15, State 1, Line 1'TRUNCATE_ONLY' is not a recognized BACKUP option.
What is the new option in SQL Server 2008?
Simple recovery model.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply