Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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
  • Advertisements

    15 Responses to “SQL Server Internals : What is TUF File in SQL Server?”

    1. Nice article sir … i faced with above error last week…

    2. Bhupendra said

      Excellent article sir, cleared my doubts

    3. Good One Sir really nice !!

    4. Vivek Kumar Srivastava said

      Very nicely explained, Balu Sir . Concept of TUF file is very much clear. Thanks again for posting , Sir.

    5. Krishnaraj S said

      Excellent explanation for TUF file…

    6. dinesh kumar said

      thank you sir for explaining in fine details

    7. Tridib Dev said

      Excellent article. I have only one doubt. The events no. 1 to 7 in the scenario has already occured. While I restore then only I am taking the decision of choosing stand by mode. How SQL server will know that I am going to use stand by mode during restore.
      I can understand the scenario well during replication.

    8. Alex said

      Great article and very thorough explanation! Thanks!

    9. vijay reddy said

      Good Article and very clear explanation

    10. Senthil Kumar said

      Excellent Article Lakhani. Thanks for that.

      by the way, I know you well in person while we both working at EAM team in Ramco Systems.

      Regards,
      Senthil.

    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: