BACKUP Log With TRUNCATE_ONLY

Question

Okay...seriously. Could someone from Microsoft who monitors this board please tell me why you decided to remove that functionality?!?!? So every time I have an issue with a log getting full, I have to go in, switch the mode to simple, and then switch it back?!?!

Pardon me for saying, but that just seems kind of a pain in the posterior...

---

A. M. Robinson

Answer 1

If you continually find your log getting full (and that is an issue), then I wonder why you are using the FULL recovery model to begin with.

If you are going to use the FULL recovery model, you *MUST* take regular transaction log backups.  If a maintenance activity is planned to grow the log too much, then take more frequent log backups during that timeframe.

If you aren't taking log backups (and that is why your log is filling up) then switch to the SIMPLE recovery model and perform database backups as often as you need.  If you are truncating the log, I suspect you don't need the transaction log for recoverability purposes anyhow, so SIMPLE may be a better option for you.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

The fact is that even WITH regular transaction log backups, sometimes the log DOES get full and needs to be truncated...

So my question again is why remove that option when there may be cases when you sometimes need to truncate a log due to some unforeseen issue?

---

A. M. Robinson

Answer 3

The fact is that even WITH regular transaction log backups, sometimes the log DOES get full and needs to be truncated...

So my question again is why remove that option when there may be cases when you sometimes need to truncate a log due to some unforeseen issue?

---

A. M. Robinson

Take another log backup and the log will no longer be full.

See these links for more information:
http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/

It is a good thing NO_LOG/TRUNCATE_ONLY were removed.

Again though, if you find yourself having to truncate the log because your drive is full frequently, then I question why the need for the use of FULL recovery model if you're just throwing away the transaction log. 

Your only other option is to issue this command, which I do not recommend either: BACKUP LOG TO DISK = 'NUL:'

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 4

The reason is really simple - it empties out the contents of your SQL Server’s transaction log without really backing it up and you are forced to compromise in data recoverability and hence the point in time recovery cannot be achieved .
Why not switch back to SIMPLE recvery mode?

---

Thanks, Leks

Answer 5

For starters, I'm no newbie to SQL Server - been using it for over ten years. I know how TRUNCATE_ONLY works. If you get a "stuck" transaction, and your log grows continually and you're unable to shrink, TRUNCATE_ONLY comes in very handy.

Secondly, I'll give you an example. If for some reason we need to do a massive load of data we receive from a new client (let's say 25,000,000 rows). The log will grow substantially. This DOES NOT happen all the time  - maybe once a month. Number one, I don't want to take a log backup - that log backup will be massive and consume disk space I may not have and take time I may not have.

My database needs to be in full recovery mode. Period. If for some reason the massive bulk load fails, I have my own method of getting that stuff back out and starting from scratch.

Thirdly, I think it's safe to assume that most seasoned database professionals know when and when not to use TRUNCATE_ONLY.

Sometimes it annoys me when Microsoft decides what's in my best interest...

---

A. M. Robinson

Answer 6

For starters, I'm no newbie to SQL Server - been using it for over ten years. I know how TRUNCATE_ONLY works. If you get a "stuck" transaction, and your log grows continually and you're unable to shrink, TRUNCATE_ONLY comes in very handy.

Secondly, I'll give you an example. If for some reason we need to do a massive load of data we receive from a new client (let's say 25,000,000 rows). The log will grow substantially. This DOES NOT happen all the time  - maybe once a month. Number one, I don't want to take a log backup - that log backup will be massive and consume disk space I may not have and take time I may not have.

My database needs to be in full recovery mode. Period. If for some reason the massive bulk load fails, I have my own method of getting that stuff back out and starting from scratch.

Thirdly, I think it's safe to assume that most seasoned database professionals know when and when not to use TRUNCATE_ONLY.

Sometimes it annoys me when Microsoft decides what's in my best interest...

---

A. M. Robinson

Regardless of whether you have used SQL Server for 1 year or 10, your responses and attitude show that your really don't understand the fundamentals behind planning FULL Recovery, or the impact of issuing a TRUNCATE_ONLY.  If you get a stuck transaction and the log grows, truncate only isn't going to truncate the active portion of log space held open by the stuck transaction.  You would have to commit/rollback/kill the transaction to allow that space to truncate out.  At that point, a log backup would clear the space allowing it to be reused. 

The forums are full of posts where people using truncate_only killed full recovery and didn't realize what they were doing, only to find out later when they actually needed their transaction log backup chain.  Removing it from the product was a great move by Microsoft to prevent this problem.  TRUNCATE_ONLY has no business in the product, like it or not, it is gone.  Forcing someone to switch to SIMPLE recovery to truncate the log contents pretty much guarantees that they know they are breaking their ability to perform a point in time recovery, since that is not possible with SIMPLE recovery.

A majority of the people responsible for managing SQL Server are IT Professionals that involuntarily were made the DBA because some product or application they now have in their business requires a SQL Server.  It is in the best interests of the primary users of SQL Server to not have TRUNCATE_ONLY functionality in the product.  If you truly need FULL recovery, you can't use TRUNCATE_ONLY without immediately taking a full backup of the database because you have no recovery chain after the point you use TRUNCATE_ONLY. 

I don't know anyone that I consider to be a "seasoned" professional that would be making an arguement for using TRUNCATE_ONLY over switching to SIMPLE mode to clear the log.  That is not intended as a personal attack, but I can provide you links from most of the big names in the SQL Server community that explain the pitfall of using TRUNCATE_ONLY and why it shouldn't be in the product.  As a side point, you can't do anything similar in Oracle when in archive logging mode because it breaks the recovery chain and they don't allow you to do that to yourself without making the distinct decision to turn off archive logging completely.  Otherwise you had better have RMAN pulling your archive logs.  Your arguments don't match most of the seasoned professionals in the community. 

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

I see your point, and I agree that most seasoned database professionals know when and when not to use TRUNCATE_ONLY.  However, I wouldn't give a steak knife to my two year old, and I think Microsoft have heard a few too many stories of people that have truncated their log and then gotten into problems associated with breaking the log chain (replication, log shipping, DR).

I see a number of reasons to remove this command:

1. It's technically not a backup option, as you're simply not backing up the log.

2. ALTER DATABASE requires more permissions than BACKUP LOG.

3. Using ALTER DATABASE (might) makes you more aware of the impact you're having

4. The functionality hasn't been removed, it's just a syntax change - and it's a two step process rather than a one step.

5. You still have to run multiple commands after your TRUNCATE_ONLY anyway - you need to take another full backup to reinitialise your log chain, so TRUNCATE_ONLY isn't a one step process.

Either way, when you run TRUNCATE_ONLY, you're taking your database out of the full recovery model, as it's now impossible to take another log backup (technically, it's still listed as the full recovery model, but it's broken as you cannot take a backup.  It's possible it will stay in pseudo-simple mode, that is, it will continue to truncate the log automatically until you take a full backup, and then will switch back to full recovery - but I'd have to test that). 

Answer 8

Either way, when you run TRUNCATE_ONLY, you're taking your database out of the full recovery model, as it's now impossible to take another log backup (technically, it's still listed as the full recovery model, but it's broken as you cannot take a backup.  It's possible it will stay in pseudo-simple mode, that is, it will continue to truncate the log automatically until you take a full backup, and then will switch back to full recovery - but I'd have to test that). 

No need to test.  http://www.sqlskills.com/BLOGS/PAUL/post/New-script-is-that-database-REALLY-in-the-FULL-recovery-mode.aspx

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 9

In addition to the other posts:

I see plenty of cases where developers just throw in this command since "they found it on the Internet" in order to keep the ldf file small. This will of coure cause havic for the DBA responsible for backup handling and integrity. Removing rhe command makes it less likely that we see ts operation thrown into various jobs without understanding the consequence. And, as already have been mentioned, no functionality was removed (we can either backup to nul, or switch to simple and back to full).

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

Answer 10

Hi All

 

Attached is a link to a Connect article where MS indicate an alternative : BACKUP LOG TO DISK = 'NUL:'

http://connect.microsoft.com/SQLServer/feedback/details/362628/backup-log-with-truncate-only-or-with-no-log-is-deprecated

 

Answer 11

Phil addressed the ability to use BACKUP LOG TO DISK = 'NUL' in his comments above:

Your only other option is to issue this command, which I do not recommend either: BACKUP LOG TO DISK = 'NUL:'

But as he says, this is not recommended.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 12

Will this command influence bad if my database mirroring is asynchronous...

---

"SQLSERVER DBA" "INDIA"

Answer 13

Which command? BACKUP command do not break mirroring, since mirroring isn't implemented using backups. It will break your log backup chain, though. If the mirror is way behing, then any the log records that the mirror is behind are treated as open transactions and will not be "truncated" by any backup command.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 14

Thanks Tibor,

---

"SQLSERVER DBA" "INDIA"

Answer 15

i can't run this command on a db with simple recovery mode. But it works on db with full recovery mode. Why is that?

Answer 16

i can't run this command on a db with simple recovery mode. But it works on db with full recovery mode. Why is that?

I have no idea which command you are asking about specifically.  If you are talking about BACKUP LOG WITH TRUCATE_ONLY, and a version of SQL Server before 2008 it would be because the log is in auto truncate mode in Simple Recovery.  However once again in case you didn't read all the Answers on this thread, you shouldn't be using this command against your databases, it breaks the log chain and is no different that changing your database to Simple Recover and then back to Full.

---

Jonathan Kehayias | Senior Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 17

have a look TRUNCATE_ONLY and NO_LOG

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum

↑ Blog Animator

Answer 18

Dude, do not let your boss see that you posted this question.

Boss:  "So what's our SQL recovery strategy look like these days?"

You:  "Well, I generally let the transaction log grow wildly until it fills up the disk at some random, probably inconvenient time, then I delete it."

Boss:  "Great, keep up the good work!"

---

"Winners never quit, and quitters never win."

Answer 19

In the IT world I inhabit we're allowed to do "backups" without change requests but not configuration changes (ie alter database.)  

So even though both "truncate only" and "simple recovery" do the same thing with respect to log truncation, we're now restricted in how we deal with this problem.  This of course creates a new impact to customers where none previously existed and adds more needless work for already busy DBAs - I shouldn't complain - this is not new in the MS realm.

I've always hated working on MSSQL for this reason:  I find myself impacted more by the ignorance of the user community of Microsoft products than any other rdbms I've worked.  From error descriptions (vague doesn't begin to describe it) and misleading troubleshooting responses to now being hindered by the lowest common denominators among the user community.

Sure, Oracle doesn't let us truncate an archived logs (not exactly analogous) but neither does it require us to keep them -- they can be deleted or moved to make space in the destination directory.  No such functionality is allowed with MS other than adding space somewhere or running a backup.  

In the MS world this is often problematic due to the very nature of it being so easy to install on non-datacenter ready hardware and storage.