undo file in logshipping | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

undo file in logshipping

hi, why do we need to specify undo file during logshipping? krishna chaitanya.s
project engineer-SQLDBA

A warm standby server can be brought up in read-only mode between transaction log restore operations if an undo file is used. Explanation from BOL:
An instance of SQL Server records many different types of information in the transaction log. Instances of SQL Server 2000 primarily log the logical operations performed. The operation is reapplied to roll forward a modification, and the opposite of the logical operation is performed to roll back a modification. Each instance of SQL Server controls when modifications are written from its data buffers to disk. An instance of SQL Server may cache modifications in buffers for a period of time to optimize disk writes. A buffer page that contains modifications that have not yet written to disk is known as a dirty page. Writing a dirty buffer page to disk is called flushing the page. When modifications are cached, care must be taken to ensure that no data modification is flushed before the corresponding log image is written to the log file. This could create a modification that could not be rolled back if necessary. To ensure that they can recover all modifications, instances of SQL Server use a write-ahead log, which means that all log images are written to disk before the corresponding data modification. If any errors occur during a transaction, the instance of SQL Server uses the information in the log file to roll back the transaction. This rollback does not affect the work of any other users working in the database at the same time. Usually, the error is returned to the application, and if the error indicates a possible problem with the transaction, the application issues a ROLLBACK statement. Some errors, such as a 1205 deadlock error, roll back a transaction automatically. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Note that if you don’t need to bring the database into read-only mode you do not need to specify an undo file. Just make sure you use RESTORE… WITH NORECOVERY instead of RESTORE.. WITH STANDBY. Karl Grambow www.sqldbcontrol.com