Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Tips and Tricks : BACKUP LOG … TRUNCATE_ONLY

Posted by blakhani on July 29, 2014


Below are the most common search terms when someone is sees a big file with extension LDF and identifies that it’s used by SQL Server.

  • LDF file is huge
  • LDF Files Too Large
  • Shrink huge LDF File SQL
  • How to Shrink LDF File

There are many article on the internet which can explain what these files are and how to reduce the size. Here is an official KB http://support.microsoft.com/kb/317375 If the size growth is due to the fact that database is in full recovery model and no log backup taken from long time then here are few common tasks are:

  • Take a transaction log backup and then shrink the LDF file.
  • Change recovery model to simple and then shrink the file.

One situation which I faced recently that we were even unable to change the recovery model of the database because the drive the completely full. There was no space so taking log backup was not an option. In earlier version of SQL there was a option called TRUNCATE_ONLY which is removed in later versions of SQL but in SQL 2008 onwards, we would get below error message.

Msg 155, Level 15, State 1, Line 2
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

Before attempting backup to NUL device (explained below) there are other thing you might want to try.

  • Add a new LDF file to database and take regular log backup and then shrink the log.
    • Above option would be a safe option as you still have log backups.
  • Change recovery model to simple and make it full again.
    • This will cause flush of inactive portion of log and you should be able to shrink the log.
    • Make sure you take a full backup immediate and this is your baseline.

In certain situations the only option would be to take backup on NUL device (Yes that single L in NUL). What is NUL device? Try creating a folder with name NUL or rename a folder to NUL, you would get below error message.

image

[Window Title]
Rename

[Content]
The specified device name is invalid.

[OK]

As per WikiPedia http://en.wikipedia.org/wiki/NUL:  The null device is a device file that discards all data written to it but reports that the write operation succeeded. … NUL: or NUL on DOS…
The very important point here is “discards all data written” which means if we take backup using below command it would be reported as success but nothing is backed up.

BACKUP DATABASE [Sales] TO  DISK = N'NUL' 


Here is what we would see in ERRORLOG

2014-07-29 07:41:07.67 Backup      Database backed up. Database: Sales, creation date(time): 2014/06/03(11:08:59), pages dumped: 315, first LSN: 48:360:37, last LSN: 48:392:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘NUL’}). This is an informational message only. No user action is required.

2014-07-29 07:41:07.69 Backup      BACKUP DATABASE successfully processed 306 pages in 0.077 seconds (31.047 MB/sec).

If we use DISK = NULL then it’s NOT The same.

2014-07-29 07:45:50.10 Backup      Database backed up. Database: Sales, creation date(time): 2014/06/03(11:08:59), pages dumped: 316, first LSN: 49:16:37, last LSN: 49:48:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\NULL’}). This is an informational message only. No user action is required.

2014-07-29 07:45:50.20 Backup      BACKUP DATABASE successfully processed 306 pages in 0.289 seconds (8.272 MB/sec).

IMPORTANT: Taking backup like this would break the log chain which means next log backup would not be restorable. Here is what I mean.

create database TestMe
go
alter database TestMe set recovery full
go
/*
Command(s) completed successfully.
*/
use TestMe
go
backup database TestMe to disk = 'F1.bak'
go
/*
Processed 296 pages for database 'TestMe', file 'TestMe' on file 1.
Processed 3 pages for database 'TestMe', file 'TestMe_log' on file 1.
BACKUP DATABASE successfully processed 299 pages in 0.363 seconds (6.435 MB/sec).
*/
use TestMe
go
Create Table TestTable1 (i int)
go
backup log TestMe to disk = 'T1.trn'
go
/*
Processed 6 pages for database 'TestMe', file 'TestMe_log' on file 1.
BACKUP LOG successfully processed 6 pages in 0.205 seconds (0.209 MB/sec).
*/
use TestMe
go
Create Table TestTable2 (i int)
go
backup log TestMe to disk = 'NUL'
go
/*
Processed 1 pages for database 'TestMe', file 'TestMe_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.001 seconds (7.812 MB/sec).
*/
use TestMe
go
Create Table TestTable3 (i int)
go
backup log TestMe to disk = 'T2.trn'
go
/*
Processed 1 pages for database 'TestMe', file 'TestMe_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.133 seconds (0.058 MB/sec).
*/

use master 
go
drop database TestMe
go


Here is the attempt to restore the backups.

USE [master]
go
RESTORE DATABASE [TestMe] FROM  DISK = N'F1.bak' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
GO
RESTORE LOG [TestMe] FROM  DISK = N'T1.trn' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
GO
RESTORE LOG [TestMe] FROM  DISK = N'T2.trn' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
GO

and the error while restoring T2 is below

Msg 4305, Level 16, State 1, Line 6

The log in this backup set begins at LSN 48000000022400001, which is too recent to apply to the database. An earlier log backup that includes LSN 48000000020800001 can be restored.

Msg 3013, Level 16, State 1, Line 6

RESTORE LOG is terminating abnormally.


Hope this gives you some idea about usage of backup to NUL device and danger as well.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Advertisements

    4 Responses to “Tips and Tricks : BACKUP LOG … TRUNCATE_ONLY”

    1. In SQL 2008, you can change the recovery model to simple even if the log is completely full. If the log is full, changing to simple recovery model is a non-logged operation. Also, the gap int he log chain can be spanned by a differential backup. If you are in a situation where ou need to do this, then immediately take a differential or full backup so you can recover the database if need be.

    2. John Couch said

      You couldn’t add a new log file onto another drive temporarily to allow processing to continue and take the log backup, then shrink? I know the purpose of the article is to show another way to clear the log, but these are last resort, no other option steps. I have run into these situations on several occasions and only 1 time have I swapped the recovery model to SIMPLE to clear the log. I was out of options and was not able to get space added quickly. These days, it would be a far rarer occasion since adding storage to a server is pretty quick and simple in “most” cases. And if the server is virtualized its even quicker.

      I do appreciate the purpose of the article, but please make sure people understand this is not a recommended approach, it is truly a last ditch, no other option stance. And if by chance you do exercise one of these options, you should ALWAYS immediately take a FULL backup of the database.

      But thanks for the article.

      • blakhani said

        In my case it was a cluster and the shared drive was full so adding a file was not an option. They were having only one database which was taking all the space.
        I have added more information based on your feedback.

        Thanks!

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: