BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server’s transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.
Why ‘truncate_only’ is not a recognized backup option.
I’ve blogged about why backup log with truncate_only is like a bear trap, and I meant it: you shouldn’t be running this command except during extreme emergencies. When you truncate transaction logs, you lose the ability to recover to a specific point in time.
Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log. This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup. You could lose hours – maybe even days – of data.
Microsoft recognized the problems with TRUNCATE_ONLY and removed this capability completely from SQL Server 2008. If you try to use this command:
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
You get an error:
?Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.
The only workaround in SQL Server 2008 is to switch the database’s recovery model to simple as shown in Books Online. This empties out the transaction log, thereby letting the DBA run a DBCC SHRINKFILE afterwards, then switch the recovery model back to full.
That solution still suffers from most of the same problems as using TRUNCATE_ONLY – the database’s recoverability is compromised. It’s just as bad of a solution, but unfortunately Microsoft can’t remove that workaround since we do need to put databases into simple recovery mode for other reasons.
Method 1: How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008
We can fake it by not writing our backup to a real device. SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere:
BACKUP LOG MyDb TO DISK=’NUL:’
Remember, we’re still not fixing anything here: whatever caused the log file to grow in the first place can happen again and put us right back where we started.
Method 2: Use Quest LiteSpeed to Fake a Log Backup
If you don’t want to switch into simple recovery mode, and if you’re using Quest LiteSpeed for SQL Server, there’s a back door workaround.
Don’t try what you’re about to see at home. We’re what you call experts. We’ve got years of experience that keeps us safe. Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability. This should only be done in cases where a log has grown out of control and must be erased or else the system may crash. In any other situation, you should consider backing up the log with conventional means.
Open a command prompt, go to the folder where the LiteSpeed engine is installed (typically \Program Files\Quest Software\LiteSpeed\Engine) and type:
sqllitespeed.exe -B log -D AdventureWorks -F e:\test.bak --nowrite
The parameters are:
- -B log = the type of backup we’re doing, database or log.
- -D AdventureWorks = the name of the database we’re backing up
- -F e:\test.bak = the name of the file we’re backing up to, except that…
- –nowrite = don’t actually write the file.
LiteSpeed will make SQL Server think it’s doing a backup, but since LiteSpeed controls what gets written to disk, it just smiles and nods while SQL Server keeps shoving information across. “Uh-huh, yeah, I got it. Keep that data comin’.”
Your data is not actually backed up. This is a giant problem. Don’t leave this script lying around where a junior DBA might see it and reuse it for regular backups. This should only be used to impress your friends with your useless knowledge of SQL Server, much like I’m doing here. Well, heck, it’s not even my knowledge – big thanks to David Gugick and James Deen of Quest Software for pointing out this trick.
P.S.: yes, I shouldn’t have even written this technique in public, but there’s a lesson here. We had some controversy earlier this week about shrinking databases, and I wanted to show how you can convey dangerous information with proper warning labels. If you don’t explain this stuff, someone can stumble across your instructions in a web search, think it’s completely okay, and do it without understanding the consequences.
onpnt August 21, 2009 | 11:25 am
Another reason for litespeed and the benefits to having chosen the product for my own installations.
)
Thanks, this is pretty cool! (as scary as the topic is
David Stein August 21, 2009 | 11:51 am
+1 for the Mythbusters Reference.
Scott Whigham August 21, 2009 | 12:40 pm
Brent – I’m a big fan and love reading your blog but this post is odd to me, for several reasons.
1) You say that “Microsoft even recognized the problems with [TRUNCATE_ONLY] and removed this capability completely from SQL Server 2008″ – is that really why they removed TRUNCATE_ONLY? My understanding was that it was removed because it is redundant now that we have the SIMPLE recovery model. I haven’t heard – I honestly don’t know – but I know of now “problems” with TRUNCATE_ONLY that led to its demise; only that switching to SIMPLE recovery model instantly has the desired effect (thus rendering TRUNCATE_ONLY surplus to requirements). If someone from MSFT would chime in and set the record straight, that would be kinda cool.
2) There’s no need to use LiteSpeed (though a nice plug) – you can use the NUL: drive: BACKUP LOG MyDb TO DISK=’NUL:’. That has the same effect – it tricks SQL into thinking that a log backup has taken place when, in fact, it hasn’t.
3) You imply that switching to SIMPLE recovery model is “just as bad of a solution [as using TRUNCATE_ONLY]” yet I would imply that your suggested solution (or my use of the NUL: drive) is a much, much worse solution. By switching to SIMPLE, you are doing it the recommended way. Using the NUL: drive or LiteSpeed is just making life far more complicated than it needs to be. Can you make a case in which it is actually better to use LiteSpeed with -nowrite than to actually go “by the book”? I cannot think of a single scenario in which using LiteSpeed is preferable. In fact, I can’t think of a single “pro”, only “cons”. If the log has “grown out of control” then switching to SIMPLE will clear out the inactive entries nearly instantly (and generally faster than a faux log backup).
In fact, I’ve covered doing this in a training video in my SQL Server 2008 DBA training video course:
* “Help! My Transaction Log Has Filled Up!” – http://www.learnitfirst.com/Course/157/Video/1855/Emergency-Full-Transaction-Log.aspx
Like I said, I do enjoy reading your blog and tweets but I think that the SQL community should be aware of (a) the alternatives, and (b) that this goes against the “recommended” way from MSFT.
Brent Ozar August 21, 2009 | 12:46 pm
Hi, Scott! Great questions.
1. I couldn’t tell you what the reasoning was inside Microsoft, but it just appears that way to me and to the folks I’ve talked to.
2. That’s a great point! That works too.
3. Absolutely, that’s why I point out repeatedly in the article that you shouldn’t use this trick. I’m just showing people that it can be done. You mentioned that doing it my way is “far more complicated” – keep in mind that my method only requires one statement. Your method requires multiple statements.
You’re absolutely, completely correct that this method is dangerous and is not recommended, and I go way, way out of my way throughout this article to point that out. If there’s anything I can say or do in here to make it more clear, I’d love to hear it. Thanks!
Scott Whigham August 21, 2009 | 6:03 pm
Brent –
Kudos for replying so quickly – I’m generally more of a lurker than not so please don’t take my only responding with something “negative” to say to imply anything other than this post inspired me to stop lurking! Hopefully we’ll get the chance to meet up at PASS or DevConn this Fall.
I know that you there is a lot in this post to let people know *not* to do it this way but here’s the thing: the page title and the H3 tags on the page contradict what you’re saying. The page title is “How to Do BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008″ but this post is not about how to do that; it’s about how to use an undocumented workaround in a third party software tool. Another example is that you have the section on how to use LiteSpeed titled “How to Do TRUNCATE_ONLY in SQL Server 2008″ yet it’s really about using LiteSpeed’s -nowrite switch, not about how to quickly mark the inactive entries in the log as free space.
Now sure – the second sentence of that section says, “Don’t try what you’re about to see at home” and talks about the “cons” but remember: it’s in a section called “How to Do TRUNCATE_ONLY in SQL Server 2008″ on a page titled “How to Do BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008″. What are people supposed to think who come here from a search for those phrases? What would *you* think if you were a junior DBA coming from SQL 2000 to 2008 trying to figure out what to do and you saw that headline and this page title? You’d probably think, “Well, this guy’s really well known and super smart so I’ll do it with LiteSpeed. Besides, he said something about SIMPLE recovery model but he didn’t show any examples so that must not be the ‘real’ way to do it.”
So you said in the comment follow-up, “If there’s anything I can say or do in here to make it more clear, I’d love to hear it.” It’s your blog, of course, so I’m only making suggestions but, to me, I’d prefer to see the page title and the title of that section made more clear, something akin to “How to Use LiteSpeed to Simulate BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008″. Or keep the page title but add a paragraph that fleshes out using the SIMPLE recovery model and taking a backup (pros/cons/code).
Thanks for taking the time to listen and keep up the great work!
Brent Ozar August 23, 2009 | 8:35 am
Scott – I love it! Your questions segue perfectly into an article in my five-part series this week on how to blog better. One of the posts talks about how to use page titles and H3 tags. I’ll give you a hint: those tools are for what people are searching for, not to summarize your content. If I put an H3 with “How to Use LiteSpeed to Simulate…” nobody will ever find this post, because DBAs aren’t sitting around asking themselves, “Gee, how could I use LiteSpeed to simulate a truncate_only?” Instead, you have to write H3′s in a way that matches what people are searching for.
I wish I had a time to do a full writeup on the pros/cons/code of switching to simple mode, but unfortunately I can’t cover everything here in full, so what I’ve done is added a link to Books Online to cover how to do that.
I applaud your participation! I’m glad you popped up out of the shadows, and you’re making the post better. If I just holler with a bullhorn and nobody responds, then my work doesn’t get any better. (Believe me, hahaha.) Look forward to meeting you!
Gail August 23, 2009 | 12:48 pm
You’ve given me a blog idea, this isn’t the first time I’ve seen the backup to nul trick (of course the others didn’t have 16 warnings on them)
Think I’ll tackle the backup to nul vs simple recovery, effects side effects and warnings.
anthony April 5, 2011 | 1:22 pm
I have tried the “new and improved” method from Microsoft…and it doesn’t work! I inherited a database whose log has grown enormous. I have tried the “set to simple”, “back to full”, “back to simple”…and the log is still gigantic.
Like you mentioned earlier, I too am a professional and I know when to use TRUNCATE_ONLY and when not to use it.
You would think that Microsoft would trust professionals to know when to use something and when not to! If that’s their reasoning they better take away TRUNCATE TABLE before somebody hurts themselves!!!!
Brent Ozar April 5, 2011 | 3:39 pm
Anthony – just changing from simple, full, simple doesn’t change the size. You also have to shrink the log.
David Stein August 21, 2009 | 12:59 pm
Nice plug for Scott too BTW.
Agent_Pepe August 21, 2009 | 1:23 pm
Interesting post… good info from Scott, too.
This is one example of why I get frustrated with SQL Server. With each new version, it seems like formal training is required (or lots of research online) to keep up with all of the changes. Regardless, this seems like a good change to make for 2008.
SQLRockstar August 21, 2009 | 1:58 pm
Brent,
I followed your recommendation, thanks for the info. Now, how do I restore from the transaction log backup I just performed?
Thanks
Brent Ozar August 21, 2009 | 1:59 pm
You have to open the computer’s cupholder, and put in your recovery CD.
SQLRockstar August 21, 2009 | 5:19 pm
Hi Brent,
I opened the cupholder as you suggested and found a cupcake. Would you like the cupcake? I can send it to you. It has blueberries. Not sure if you are allergic.
I placed my recovery CD into the cupholder but nothing happened.
I need to recover my data quickly. What should I do next?
Thanks
Wes Brown August 21, 2009 | 3:29 pm
Just a note we put the –nowrite into Litespeed for internal performance testing only that’s why it isn’t documented.
Gail August 21, 2009 | 5:14 pm
While I haven’t tested the –nowrite, my gut feel is it and the backup to nul are both way, way worse that switching to simple recovery or (on SQL 2005) backup with truncate only.
If, on SQL 2005, someone executes a backup log with truncate, all log backups after that fail with an error message (this unfortunately was not true on SQL 2000). Hence it’s pretty apparent that the log chain is broken.
BACKUP DATABASE [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.bak’
GO
BACKUP LOG [Testing] WITH TRUNCATE_ONLY
GO
BACKUP LOG [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.trn’
GO
The last one returns the error
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
A log backup without writing or made to the nul device does not tell SQL that the backup chain is broken. Because it thinks that the log backup was written to disk, subsequent log backups succeed and create files.
BACKUP DATABASE [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.bak’
GO
BACKUP LOG [Testing] TO DISK=’NUL:’
GO
BACKUP LOG [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.trn’
GO
The last log backup succeeds.
If one tries to restore a DB (with logs) where someone has done a backup to nul, no surprise, it fails
Restore Database Testing From Disk = N’D:\Develop\Databases\Backups\Testing_2005.bak’ WITH NORECOVERY, REPLACE
RESTORE LOG Testing FROM Disk = N’D:\Develop\Databases\Backups\Testing_2005.trn’ WITH RECOVERY
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 1611000000015500001, which is too recent to apply to the database. An earlier log backup that includes LSN 1611000000015000001 can be restored.
I think it needs to be made crystal clear (anywhere that the ‘trick’ of backing up to nul is mentioned) that it does not have the same effect as Backup Log with truncate only or a switch to simple recovery.
Hence, if the log really does need truncating, please, please, please, rather switch to simple and back to full rather than playing tricks like the backup to nul and leaving a log chain that looks intact but is totally useless because of a file that was written nowhere.
Brent Ozar August 21, 2009 | 5:30 pm
Gail – I totally agree that it’s dangerous, and that’s why I put in bold print in the last paragraph, “Your data is not actually backed up.”
At the same time, whether you switch to simple mode or if you do this trick, you can’t recover until you do another full backup. Whether you do log backups and they succeed or fail, they’re equally worthless in this scenario. It’s certainly dangerous to think that your log backups worked successfully, and that’s why I wanted to emphasize that your data is not backed up.
Personally, I like this solution (instead of the simple mode switch) because I could fire this off with a single one-line command, then restart my backup jobs as if nothing ever happened. If I know I don’t have recoverability anyway and that I can’t do another full backup until my normal nightly backup window, this is a way of fixing it right away without having job failure alarms go off all over the place.
I don’t want to make it sound like I think this whole scenario is a good idea. No matter what you do, no matter which method you take, when you dump the log, you’re screwed and your log data is gone forever.
Gail August 21, 2009 | 5:49 pm
The difference is that it’s far less obvious afterwards that something’s wrong if it’s done this way.
Brent Ozar August 21, 2009 | 5:51 pm
Yes, I agree with you there. If you work in a shop with multiple DBAs, you should definitely use the recovery model route just so everyone knows what you did.
Bill December 21, 2012 | 12:56 pm
I know this is an old post, but I want to verify something. You say that there is no way to recover without a new full backup. My understanding is you can use even a differential backup to restart the chain without the need for a full. Is this not correct?
Wes Brown August 21, 2009 | 5:28 pm
Gail,
You are 100% right. The –nowrite is the same is backup to NUL: SQL Server thinks there was a valid backup taken where a truncate_only or flip to simple is a known quantity and changes the recovery point for the database.
Brent Ozar August 21, 2009 | 5:46 pm
I’m still sitting here laughing at this whole thing. Everybody’s missing the point of the blog post. Ordinarily, I never would have even posted this technique in public, but when I saw that SQL Server Magazine publishing an article on how to shrink your database files without so much as mentioning that there might be drawbacks, I couldn’t sit idly by. Editors have a responsibility to filter dangerous information and make sure that it either doesn’t get published or that it comes with plenty of warnings about the dangers involved.
Go through this post and count the number of times I warn the reader that there’s drawbacks and dangers. I even put in bold print, “Don’t try what you’re about to see at home.” How much more specific can I be? Do you want me to use the blink tag? Have an audio soundtrack on the page with screams of terror?
The point of the post is to show how to provide dangerous information. If you absolutely have to provide dangerous information, do it with tons of warnings. Let’s not get sidetracked by the fact that it’s dangerous information – we all agree it’s a crazy trick.
Gail August 21, 2009 | 5:50 pm
I got your point, just adding additional warnings about a facet of this that I thought wasn’t quite clear enough.
Scott Whigham August 21, 2009 | 6:10 pm
The point is muddled – let’s just call it muddled since I’m about to go muddle a nice mojito. Your page title and the H2/H3 tags say one thing while your paragraph text and comments say something else. And since we all know that search engines l-o-v-e page titles and H2/H3 tags, this post is bound to rank for anyone search for “TRUNCATE_ONLY in SQL Server 2008″ – and if they aren’t careful, they could have picked up some of that “dangerous information” you mentioned!
Wes Brown August 21, 2009 | 6:02 pm
And my point was you are using an undocumented feature of a third party product in a way it was never intended for and why it is in the product.
After that I was just confirming Gail’s assumption about the –nowrite.
I almost put the same thing in my first comment but didn’t think it was completely needed since you mention several times that your database will be FUBAR’ed if you do this.
Oh, one last thing.
This trick is extremely dangerous….
-wes
Craig O'Brien March 12, 2010 | 10:00 am
Just wanted you to know, I found this stream of argument to be hilarious and petty, like an episode of Seinfeld.
My company is small we have no DBA; I handle the tasks a real DBA probably would at a larger company. Even I knew enough to not us truncate_only in production. I did once years ago in an emergency, but immediatly did a full backup.
We did use truncate only regularly on dev databases to save space (ususally after restoring from production.) We don’t need those backed up at all.
Thanks for the BACKUP LOG @dbName TO DISK=’NUL:’ it is perfect for my need, and I wouldn’t have found it if not for the heading and if not for the reply so thanks to both Brent AND Scott.
BigSQL August 12, 2010 | 10:11 am
Its been a few months and we’ve all had a chance to simmer down…
I for one am happy to have found the code tidbit “TO DISK = ‘NUL’” to add to my error generating “BACKUP LOG MyDatabase” – I am testing TDE and having to backup. restore, encrypt, unencrypt.
I can tell you all that many DBA’s that inhabit corporations of America have broken the chain of backups with the “truncate_only” and caused jobs to fail…some may have never known…
This tidbit is useful in a testing environment with backup jobs/maintenance plans, when backups are made not to recover data from disaster, but to test encrypted backups, encryption options, compression, etc.
So I would add to all the warnings and other thoughts that the LiteSpeed trick and/or the NUL disk trick are good to know for the well-rounded DBA, kind of like shooting a snake with a six-gun.
usman August 18, 2010 | 2:10 am
I want Single table backup in a particular database
in sql server 2005, its possible r not
Brent Ozar August 18, 2010 | 7:27 am
Usman – not easily, no. You can put the table on its own filegroup, and then back up that filegroup, but it’s not necessarily easy to restore just that one table by itself.
USMAN August 21, 2010 | 12:13 am
Hi
I have only client operating system (windows xp) but i have sql server 2005 in that system ,so how to config the log shipping my system sql server to other system that is also client thats having sql server.please give the step by step process if u having screen shorts
EugeneZ September 1, 2010 | 12:39 pm
Hi,
Simple mode in sql server 2008 sp1 cum patch 9 does not work.
As you said:
Microsoft recognized the problems with TRUNCATE_ONLY and removed this capability completely from SQL Server 2008.
—
and it is a big deal to shrink \truncate trans log file with Gs initial size
sukesh October 6, 2010 | 1:10 am
For SQL 2008
Use
GO
Alter Database Set Recovery Simple
GO
Alter Database Set Recovery Full
GO
DBCC SHRINKFILE (”, 1)
GO
BrentOlsen November 29, 2010 | 9:47 am
So I backup transaction logs several times daily, do a nightly backup, then on my weekly maintenance after the weekly complete backup and reindexing I truncate the log because the maintenance makes it so large before I start the weekly routine again. Is there a better option? The maintenance creates a log that consumes my disk and the boys in IT hate to allocate more SAN…
Brent Ozar November 29, 2010 | 5:25 pm
Brent – if you keep using the log space every week, why do you keep shrinking it? You’re going to need it again next week for the next maintenance window, and growing the log file is a dead-slow operation in SQL Server because Instant File Initialization doesn’t work for the log file.
Mohammed Muddassir April 14, 2011 | 2:18 am
Hi Brent,
So, the conclusion or actual (as assumed) solution would be to set the recovery to simple, back to full and dbcc shrinkfile?
I got an MSS 2008 R2 db hosting sharepoint, OCS and few other infrastructure services. The log disk is 102 GB and its almost full. Fortunately, our server admin gave us 2 more gbs of space. One of my friends suggested me to do dettach thedata file and log file and move the log file to a new drive or disk followed by a full restore after the db backups. What is your take on it?
Thanks
Mohammed
Brent Ozar April 15, 2011 | 9:39 am
Mohammed – Correct. For faster answers, try posting your questions on a major forum site like http://ServerFault.com or http://SQLServerCentral.com.
JB April 14, 2011 | 2:28 pm
Brent, thank you for the blog, you make great points, often…
I have to say since our transition from SS2005 to SS2008R2 I sorely needed the DISK= ‘NUL:” parameter to fake out SS (as was also recommended by Scott W much earlier) for my data movements to work. Without it I often found my db becoming overwhelmed by the large data moves we deal with.
I have been working through a data warehouse staging system where we load, cleanse and transform data before moving to warehouse and analytic facts and dimensional objects.
One of the great features of the TRUNCATE_ONLY option was for me to keep the staging logs fit and trim, since of course the data is coming from someplace and going to someplace else.
In my experience the big no-no’s of lost logs has everything to do with OLTP or finalized OLAP db’s and little with transitional data bases fed by a consistent source and feeding data to yet a different consistent target.
Is the kind of work I’m doing that much of the exception or am I thinking this wrong…. thoughts and recommendations?
Brent Ozar April 15, 2011 | 9:41 am
JB – the work you’re doing is usually called ETL or ELT, and that work is not usually done in the same database as production. When you need to load staging data, use a separate staging database and leave it in simple recovery mode.
Anthony April 20, 2011 | 5:41 pm
Let me give an example of what has really got me peeved!! I restored a copy of our production database onto a development server. When restoring the backup, the log file is 68GB in size. I don’t want nor do I need a log file – especially one that large on a development box.
I have changed the mode from FULL to SIMPLE. I then did a DBCC SHRINKFILE of the log file. The log file is still 68GB in size. I have tried this method about ten times – the file is not shrinking.
I’m so glad they got rid of TRUNCATE ONLY…
Tom L April 21, 2011 | 2:59 am
Thanks for all of the above. However would anyone have an idea of how to shrink tempdb log files? I say this because one cannot run a backup on tempdb, full or simple. A current problem we are having is the tempdb log fulls up and crashes one of our import procedures when moving the data from staging to live. Running on SQL server 2008 R2.
Robert Gale February 20, 2012 | 3:01 am
Just so it’s to hand, we use the following script to ‘mimic’ truncate_only:
ALTER DATABASE SET RECOVERY SIMPLE WITH NO_WAIT
GO
dbcc shrinkfile(”)
GO
ALTER DATABASE SET RECOVERY FULL WITH NO_WAIT
(We have copies of production systems used by our customers for training, test-and-trial, etc – which don’t need the log, but which we keep as mimics of the live environment – hence at least one ‘legitimate’ reason for this recovery-but-no-recovery situation.)
Phil May 17, 2012 | 3:28 pm
When bulk loading data into my SQL database I like to clear the log after each table is loaded.
Thank-you for the ” Backup LOG MyDB TO DISK=’Nul;’ ” command line.
Pingback: ‘TRUNCATE_ONLY’ is not a recognized BACKUP option. | David's Interblag
Madhu January 9, 2013 | 5:19 am
Below commands works fine
ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE(‘ExampleDB_log’, 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL