In the SQL Server 2005 the BACKUP LOG WITH TRUNCATE_ONLY statement was marked depricated, but still worked. In SQL Server 2008 it returns an error.
BACKUP LOG testtrunc WITH TRUNCATE_ONLY
Msg 155, Level 15, State 1, Line 3
'TRUNCATE_ONLY' is not a recognized BACKUP option.
So how can we truncate the logfile in SQL Server 2008? Well, just switch to SIMPLE RECOVERY and back to FULL.
ALTER DATABASE testtrunc SET RECOVERY SIMPLE
ALTER DATABASE testtrunc SET RECOVERY FULL
Be carefull, don't run this on an production database. You are erasing the transaction log file and will loose the possibility to do point in time restores.
Subscribe to:
Post Comments (Atom)
2 comments:
I ran the two ALTER statements and the log file size did not change one iota. Is there a missing step here?
Graham,
If want to reclaim the diskspace, you need to run DBCC SHRINKFILE. Check out this KB article: http://support.microsoft.com/kb/907511
Post a Comment