# Thursday, June 24, 2010

Sql 2008 'truncate_only' is not a recognized BACKUP option.

If you have ever had to truncate a log in Sql 2005, the 'with truncate_only' is fairly familiar. Unfortunately for you, if you have it in your scripts, it has been removed in Sql 2008. 

You can use the Sql Management Studio to shrink the log files, I can post later about how to do this, but for some databases it will appear not to work (even if it throws no error). The log files stay the same size.

What may be the issue is that the Database is using a Full vs a Simple backup recovery method. You can find out more about the difference between the two here -> http://msdn.microsoft.com/en-us/library/ms187048.aspx. What we are concerned about is the note show below:

NoteNote
Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each checkpoint but can be delayed under some conditions. For more information, see Transaction Log Truncation.

What this means in the short run is that by switching your backup recovery method to Simple, you will instantly be able to shrink your log file.

Ex: Alter Database %your Db Name Here% Set Recovery Simple

I would be remiss if I did not mention this note from the page on the Simple recovery method:

Important noteImportant
The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, we recommend that you use the full recovery model. For more information, see Backup Under the Full Recovery Model.

One could always switch it to Simple, truncate, and switch back to Full, but it would probably be better to keep it at Full and establish a more functional means to keep those pesky log files small...

All comments require the approval of the site owner before being displayed.
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, strike, strong) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview