What is BACKUP LOG WITH NO_LOG ? its usage

What is BACKUP LOG WITH NO_LOG ? its usage

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY – they’re synonymous) to allow log truncation.

How is it used?

The common use is when the transaction log grows to be inordinately large on a database that’s in FULL (or BULK_LOGGED) recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won’t truncate until its been backed up. In these circumstances, if you don’t take a transaction log backup, the log will continue to grow until it runs out of disk space.

The alternative to taking a real log backup is to issue a BACKUP LOG dbname WITH NO_LOG command. Let’s see this in action – note that I’m not advocating its use but I want to show you what it does. First off I’ll create some transaction log after taking a full database backup:

USE nologtest;GO

BACKUP DATABASE nologtest TO DISK = ‘c:\sqlskills\nologtest.bck’ WITH INIT;GO

CREATE TABLE t1 (c1 INT, c2 VARCHAR (8000))GO

SET NOCOUNT ONGO

DECLARE @a INT;SELECT @a = 1;WHILE (@a < 10000)BEGIN
   INSERT INTO t1 VALUES (@a, REPLICATE (‘a’, 8000));
   SELECT @a = @a + 1;END;GO

How large is the transaction log now?

SELECT name, size FROM sys.database_files;GO

name            size
————— ——–
nologtest       90264
nologtest_log   104128

A little bit larger than the data file (which is what I’d expect after the operation I just performed) and they’re both around 100MB. I’ll pretend that I haven’t been paying attention to the size of the database and log and now I don’t have any space to perform a backup. Can I just shrink the log?

DBCC SHRINKFILE (nologtest_log, 2);GO

Cannot shrink log file 2 (nologtest_log) because all logical log files are in use.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
—— ———– ———– ———– ———– ————–
11     2           102944      63          102944      56

No. What’s stopping me (well DBCC SHRINKFILE just told me, but let’s double-check)?

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘nologtest’;GO

LOG_BACKUP

Ok – so I can’t perform a backup so I’ll use BACKUP LOG WITH NO_LOG.

BACKUP LOG nologtest WITH NO_LOG;GO

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘nologtest’;GO

NOTHING

Now it looks like I can do the shrink:

DBCC SHRINKFILE (nologtest_log, 2);GO

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
—— ———– ———– ———– ———– ————–
11     2           256         63          256         56

Success!

Hold on, are you sure?

Why is it bad?

Do you realize what just happened? We discarded the contents of the transaction log since the last full/differential/log backup. That means that until the next full or differential backup completes, all work since the last backup completed will be lost if the database is destroyed. So if your business relies on not losing data/work in the event of a disaster – then what I just did is sacrilege!

The whole point of FULL (and BULK_LOGGED) recovery modes is to preserve the transaction log so that the database can be recovered right up to the point of a failure, or to do point-in-time restores. Using BACKUP LOG WITH NO_LOG negates all of that. (For completeness, note that you can’t do a point-in-time restore to any time in a transaction log backup containing a bulk-logged transaction).

What are the alternatives?

If you don’t want the FULL recovery mode behavior, then don’t use FULL recovery mode – switch to SIMPLE mode. But be aware that you lose the ability to restore to any point in between your full or differential backups.

If you want the FULL recovery mode behavior, but don’t want to run out of log space – then the answer is again simple (pun intended :-), take log backups! If your log is growing too quickly, take more frequent log backups! If you don’t have enough disk space to store the log backups, talk to your management about the options – buying more disks and continuing to use the FULL recovery options, or being forced to move to SIMPLE recovery mode.

One thing to bear in mind – you actually have to monitor the size of your log to tell whether its growing. That’s what gets people into trouble in the first place – a combination of:

• Not knowing that the database is in FULL recovery mode
  
• Not tracking the size of the log

The problem comes for those people who just use an app and have no idea that the developer put the database (maybe for a web application log) into FULL recovery mode, or even that they have SQL Server on their system. For these people there is no good answer unfortunately apart from educating developers.

How to prevent it being used

If you’re a sysadmin who wants to stop your database owners and backup operators from using the NO_LOG option, there is way to do it. Trace flag 3231 in SS2000 and SS2005 will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. Trace flag 3031 in SS2005 turns them in checkpoints in all recovery modes. These trace flags are undocumented but they’re allowed to be publicized.

This way no-one can truncate the log unless you allow them to, and when they come to you to complain, you can educate them!

Ref:
http://www.sqlskills.com/blogs/paul/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it/