Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing 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
  • Posted in Tips and Tricks | Tagged: , , | 4 Comments »

    SQL Server Internals : What is TUF File in SQL Server?

    Posted by blakhani on July 24, 2014


    While answering on few forums I have realized that there are some variation of same question which I used in title of the blog.

    • What is .TUF file in Log Shipping?
    • I deleted TUF file, any problem?
    • I am not using log-shipping, still I see a TUF file.
    • What is the location of TUF file in log shipping?

    TUF file stands for Transaction Undo File in SQL Server. I will try to explain little more so that you can understand the usage and importance of it.

    When we perform a restore of database backup files, we have three options.

    • with recovery – database would be recovered and no further backups can be applied.
    • with norecovery – database would not be recovered and further backups can be restored. NOT be accessible to users for read operations.
    • with standby– database would not be recovered and further backups can be restored. Accessible to users for read operations.

    If SQL has to allow read operation to users, it has to show only those committed data. Let’s assume a scenario to understand better.

    1. Created Employee Table.
    2. Inserted 5 employees and transaction committed.
    3. Took full backup which got completed. (F1)
    4. Started Transaction and Inserted 5 more employees
    5. Took Transaction log backup and completed (T1)
    6. Committed Transaction which was started in step 4.
    7. Took Transaction log backup and completed (T2)

    If we perform restore operation of F1, we have to use either norecovery or standby because if we use “with recovery”, we won’t be able to restore other backups. Lets assume that we use “with standby”. This time a TUF file is created but would not contain much information because there is no uncommitted transaction when backup was taken. Since we are able to open database, we should be able to see 5 employee. So far, we are clear. Next, if we go ahead and apply T1 with standby and then open the database, should we see those new 5 records also? No, we should not because they were part of a transaction that was uncommitted when T1 was completed. The information stays in TUF file. When the next transaction log backup T2 is restored, SQL Server would use this TUF file along with T2 to roll forward that particular transaction. So we need both the files during restore. Now you can imagine a situation and implication of missing a TUF file. Yes, you would not be able to restore the log backups chain and need a fresh backup to be taken.

    In log shipping scenario if we choose “Standby” option for the secondary server then the transaction log backup file would be restored in standby mode. As discussed earlier, this would need a TUF file to be generated automatically. The location of this file is not configurable and it has been changed in few versions of SQL. Refer KB 

    Now a major question would be, how can I find the location of the TUF file given in last restore? I use a little trick here. If you are aware of default trace feature in SQL Server, you might know the restore command is logged into default trace. I have taken this idea from Standard Reports in SSMS.

    declare @enable int;
    select @enable = convert(int, value_in_use) from sys.configurations where name = 'default trace enabled' 
    print 'default trace is NOT enabled.'
    if @enable = 1  
    begin 
            declare @curr_tracefilename varchar(500);
            declare @base_tracefilename varchar(500);
            declare @status int; 
            declare @indx int;   
            declare @temp_trace table ( 
                    Error int
            ,       StartTime datetime
            ,       HostName sysname collate database_default null
            ,       ApplicationName sysname collate database_default  null
            ,       LoginName sysname collate database_default null
            ,       Severity int
            ,       DatabaseName sysname collate database_default null
            ,       TextData nvarchar(max) collate database_default 
            ); 
            
            select @status=status, @curr_tracefilename=path from sys.traces where is_default = 1 ;
            set @curr_tracefilename = reverse(@curr_tracefilename) 
            select @indx  = patindex('%\%', @curr_tracefilename)  
            set @curr_tracefilename = reverse(@curr_tracefilename) 
            set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 
    
            
            select  StartTime
            ,        TextData  
            ,       HostName
            ,       ApplicationName
            ,       LoginName
            ,       DatabaseName    
            from ::fn_trace_gettable( @base_tracefilename, default ) 
            where EventClass = 115
            and TextData like '%Restore%Standby%' 
            and ServerName = @@servername
            order by StartTime
    
    end
    

     

    Here is the output from my SQL Instance.

    image

    If the undo file is missing, SQL Server would raise below message. Note that TUF extension is not mandatory.

    Msg 3013, Level 16, State 1, Line 8

    RESTORE LOG is terminating abnormally.

    Msg 3441, Level 17, State 1, Line 8

    During startup of warm standby database ‘TestBackup’ (database ID 6), its standby file (‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestBackup_RollbackUndo_2014-07-24_06-58-39.bak’) was inaccessible to the RESTORE statement. The operating system error was ‘2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.

     

    Hope this clears some questions you might had before reading this blog. For more, you can post comment and I shall try to answer.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in backup, Restore, SQL Server | Tagged: , , | 6 Comments »

    Tips and Tricks : Restore Using TRY…CATCH might fail with error

    Posted by blakhani on July 22, 2014


    Few days back one of my friend called me with an interesting problem. She told that when she does a regular restore of a database backup file, it works but when she does using TRY/CATCH block, it fails. The very first thing which I asked her– What is the error message? She sent me email with below message. I have changed some names to save the innocents.

    <EMAIL>

    Command which fails.

    BEGIN TRY  
        RESTORE DATABASE [TestBackup] 
        FROM  DISK = N'C:\Temp\TestBackup.bak' 
        WITH  FILE = 1,  
        MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
        MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
        NOUNLOAD,  
        STATS = 5; 
    END TRY 
    BEGIN CATCH  
                 THROW; 
    END CATCH  

    Here is the output

    52 percent processed.

    100 percent processed.

    Processed 240 pages for database ‘TestBackup’, file ‘TestBackup’ on file 1.

    Processed 2 pages for database ‘TestBackup’, file ‘TestBackup_log’ on file 1.

    Converting database ‘TestBackup’ from version 611 to the current version 706.

    Database ‘TestBackup’ running the upgrade step from version 611 to version 621.

    Database ‘TestBackup’ running the upgrade step from version 621 to version 622.

    Database ‘TestBackup’ running the upgrade step from version 622 to version 625.

    Database ‘TestBackup’ running the upgrade step from version 625 to version 626.

    Database ‘TestBackup’ running the upgrade step from version 626 to version 627.

    Database ‘TestBackup’ running the upgrade step from version 627 to version 628.

    Database ‘TestBackup’ running the upgrade step from version 628 to version 629.

    Msg 102, Level 15, State 1, Procedure usp_logError, Line 11

    Incorrect syntax near ‘200001’.

    Msg 3013, Level 16, State 1, Line 11

    RESTORE DATABASE is terminating abnormally

    Command which works

    RESTORE DATABASE [TestBackup] 
    FROM  DISK = N'C:\Temp\TestBackup.bak' 
    WITH  FILE = 1,  
    MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
    MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
    NOUNLOAD,  
    STATS = 5;

    There is NO difference in restore command. Only difference is that it is wrapped in TRY block. If any exception raised, it would be caught and thrown.

    <EMAIL>

    Since I see version upgrade messages I asked if backup was taken on lower version and restore is performed on higher version? She informed that this backup is taken on SQL Server 2005 and she wants to restore on SQL Server 2012. I also asked her to share ERRORLOG and here is the snippet

    2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

    2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 102, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.


    Exception 102 is “Incorrect Syntax” error. If we read the message in restore output carefully, we can see that there is a procedure usp_logError which seems to be a problem. I asked her to look at the code of usp_logError and search for 200001 and she found below:

    raiserror 200001 'Debit and Credit Mismatch'

    If we execute above piece of code in SQL 2005, it works and it’s valid whereas, it gives syntax error in SQL 2012. I looked into documentation and found below at http://msdn.microsoft.com/en-us/library/ms144262.aspx (Discontinued Database Engine Functionality in SQL Server 2014)

    Transact-SQL

    RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

    Rewrite the statement using the current RAISERROR(…) syntax.

     

    I asked her to change the syntax and modify the stored procedure before taking backup. Once done, the restore with and without TRY CATCH worked like a charm.

    You can easily reproduce this error by creating below database and procedure in SQL 2005.

    Create database TestBackup 
    go 
    use TestBackup 
    go 
    Create Procedure FailureProc 
    as 
    begin 
    RAISERROR 20001 'ERROR RAISED' 
    end 
    
    

    Restoring this backup in SQL 2012 using TRY CATCH would fail.

    Tip: Whenever I see any “Error: 928, Severity: 20, State: 1.” I always look for the exception code. In this case it was 102. To convert code to text, we can use sys.messages catalog view (check image below) . While searching further, I was able to find below variations.

    2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

    2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 156, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.


    image

    Error 156 – Incorrect syntax near the keyword ‘%.*ls’.

    There might be many exceptions raised even without try…catch, you need to read the message and look for explanation of exception code.

    You might ask, What should be done before upgrade to avoid such situations? You should run upgrade advisor. http://msdn.microsoft.com/en-us/library/ee210467.aspx (SQL Server 2014 Upgrade Advisor). There are versions available for SQL 2012 and earlier as well. They mostly catch all errors which might come after upgrade. Interestingly, RAISERROR problem is not caught by upgrade advisor.

    http://connect.microsoft.com/SQLServer/feedback/details/708167/somewhat-incompatible-t-sql-on-sql-server-2012-rc0

    http://connect.microsoft.com/SQLServer/feedback/details/694484/denali-ctp3-upgrade-advisor-misses-deprecated-raiserror-syntax

    You can vote up if you think it would help.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Upgrade | Tagged: , , , , , | 2 Comments »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 779 other followers