Help: SQL Server

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.

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.