Help: SQL Server

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.

[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.