Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘backup’ Category

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: , , | 11 Comments »

    Coverage Report: SQL Server Bangalore User Group Meeting # 2 – @ 22 December 2012

    Posted by blakhani on December 23, 2012


    After missing first SQL Server Bangalore UG meet due to team outing, I was ready to meet all my old friends at second SQL Server UG meet yesterday. First of all, Thanks to Vinod (B|T) for starting the idea of SQL Server Bangalore User Group and making it popular on social networking (Facebook www.facebook.com/groups/SQLBangalore and Twitter #SQLBangUG) I have been active on facebook page to help someone who is in need. Feel free to join the group.

    As expected, I was able to meet Kamlesh (B|T), Manas (b|t), Sahal (b|t), Sandip (b|t), Sudeepta (b|t) (names are in alphabetical order) from the community. We missed Pinal (b|t)this time as he was not available in town.

    Here is the highlights of the whole event as tweeted by me and others. Series of back to back session were suppose to start at 10:30 AM IST and room was getting full. Due to change in venue (from Signature building to Embassy building) there were some folks who were little late.

    image

    Myself and other speakers were sitting at last bench.

    image imageimage

    First session was by one of our expert, Kamlesh. His session “Better together: Excel + SQL for Database Developers” was full of demo.

    imageimage

    image

    Second session was by Microsoft Premier Field Engineer, Sourabh Agarwal (b|t). His session, Intro to Indexing – Basics rediscovered, was really to cover the basics and show the internals.

    • @napsterreturns presenting on Indexes (11:02 AM)

    imageimage

    • @napsterreturns talking about IAM page (11:04 AM)
    • @napsterreturns talks about data page and index page types. If you didnt know IAM is pagetype 10.(11:10 AM)
    • IAM page id is 10, data page id is 1, index page id is 2 (11:10 AM)
    • @napsterreturns showing DBCC IND and DBCC PAGE outputs.#SQLBangUGmeeting … Mixed extends discussed now (11:12 AM)
    • demo on uses of DBCC IND@napsterreturns (11:12 AM)
    • Now Non-Clustered Index definition. How is structure for Clustered Index and Heap rows. @napsterreturns (11:20 AM)
    • what is plan for queries written?@napsterreturns discusses the basics of Clustered, Non-Clustered and Heap using queries … (11:27 AM)
    • SQL is a cost based optimizer @napsterreturns the basics of INDEX simplified with lots of DEMOs. (11:30 AM)
    • What a perfect timing.. at 11:30 – session was ended. (11:30 AM)

    Again, a big round of applauses and Vinod introduced our next speaker, Amit Banerjee (b|t) and his session was “Knowing sp_server_diagnostics output” A feature which is not known to mant DBA. He showed some fantastic demos and shared his nice reports to read that information.

    imageimage

    • @banerjeeamit started his session at #SQLBangUG"System Health Session” (11:36 AM)
    • @banerjeeamit shwoing the System health check using "Extended Events" (11:37 AM)
    • @banerjeeamit shows the Extended Events for System Health. Explains the details.#SQLBangUG. Blocking using Extended Events … Demo !!!(11:38 AM)
    • @banerjeeamit talks about Logon errors, Netowrk erros, Lock Waits using default System Health session. (11:47 AM)
    • How can we find the CPU and Memory of SQL Server 15 mins back? @banerjeeamit is making #SQLBangUGmembers think !!! It is Ring Buffer DMV. (11:49 AM)
    • @banerjeeamit showing magical report generated with system health session#SQLBangUG learning Free monitoring!! (11:52 AM)
    • Corrupt pages can also be tracked via sp_server_diagnostics.@banerjeeamit in#SQLBangUG. Now showing failed IO sub system. (11:56 AM)
    • When the issue happened, long IO, which file …SQL Server Health Session Dashboard …. #SQLBangUGby @banerjeeamit (11:56 AM)
    • Great session by@banerjeeamit#SystemHealthSessions (12:01 PM)

    And then, I was the next one to speak about, Basics of Backups with SQL Server. My topic was not so new to deliver and to make it more interesting I added few interesting slides. I was able to keep audience awake after 3 heavy sessions. Here is the commentary of my session.

    imageimageimage

    After my session, we had enough time left before close-out so Vinod did a session about Excel feature Flash Fill. All in the room were shocked and surprised with this feature. Here are the highlights of his amazing session.

     

    imageimage

     

    Here are the tweets which came in after the meeting.

    imageimage

    Presentation content for the session is here:

    Amit – Refer his blog
    Balmukund – Basics of Backups with SQL Server
    Kamlesh – Better together: Excel + SQL for Database Developers
    Sourabh – Intro to Indexing – Basics rediscovered
    Vinod – no ppt only live demos.

    That’s all from my side. This was Balmukund, reporting on wordpress about SQLBangUG#2. Hope you have enjoyed it!

    Cheers,
    Balmukund

    Posted in backup, download, Events, ppt, presentation, SQL Server, SQL Server User Group, SQLBangUG | 15 Comments »