Friday, May 6, 2011

TRUNCATE_ONLY in SQL 2008

BACKUP LOG WITH TRUNCATE_ONLY is no longer supported in SQL v2008.  In previous versions, this command would empty your transaction logs, without backing them up.  Unfortunately, if you truncate your log, you lose the ability to recover beyond a certain point in time.  I'm guessing that is probably one of the reasons Microsoft removed the function from v2008.

There is a method to be used in v2008, but in my opinion, your recovery is still jeopardized.  If you MUST do it, then you need to alter the database recovery model to SIMPLE, in order to empty the transaction log.  You can then run DBCC SHRINKFILE to recover disk space, and then switch your recovery model back to FULL.  See here:


   /* TRUNCATE LOG BY CHANGING RECOVERY MODEL */
   ALTER DATABASE YourDatabase 
   SET RECOVERY SIMPLE;
   GO

   /* SHRINK THE DATABASE LOG FILE TO DESIRED SIZE */
   DBCC SHRINKFILE ('YourDatabase_Log' , 25) ; -- <<CHANGE AS NEEDED

   GO


   /* CHANGE THE RECOVERY MODEL BACK TO FULL */
   ALTER DATABASE YourDatabase 
   SET RECOVERY FULL;
   GO


Please understand, if your database is in SIMPLE recovery mode, the transaction log is automatically truncated.  This is only necessary if you are not using SIMPLE recovery mode.

See this list of other functions that are no longer available in v2008:   Discontinued Functionality

No comments:

Post a Comment