Twitter Updates

    follow me on Twitter

    Saturday, June 09, 2007

    SQL 2008 - Truncating the logfile

    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.

    2 comments:

    Graham said...

    I ran the two ALTER statements and the log file size did not change one iota. Is there a missing step here?

    Frederik Vandeputte said...

    Graham,

    If want to reclaim the diskspace, you need to run DBCC SHRINKFILE. Check out this KB article: http://support.microsoft.com/kb/907511