SQL Server Performance

Use of Undo file

Discussion in 'Getting Started' started by prabhabk, May 21, 2007.

  1. prabhabk New Member

    Hello Guys,

    What is the use of undo file and when it will get created? If i need to perform the restore option with standby i need a undo file.What is the contents of this file?

    i have read BOL it states that

    Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

    STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.

    If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.

    The same undo file can be used for consecutive restores of the same database

    I am not able to understand the concept..BOL says that if the undo file doesn't exist SQL Server will creates it.?How it will be possible?

    I'd appreciate all your help regarding this.


    Thanks,
    Prabhakaran
    MS SQL Server DBA
  2. satya Moderator

    Yes it will create if you haven't specifed the one, this is thefile containing information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. prabhabk New Member

    Thanks a lot

    Thanks,
    Prabhakaran
    MS SQL Server DBA

Share This Page