May 14, 2012 at 11:34 pm
I have a secondary log ship database which is always in restoring mode. All the logshipping are running without problems but I have no idea why the database is always on restoring. I have tried re-creating the logshipping and this did not help. All other logshipping database does not have this problem.
Any assistance is appreciated.
May 15, 2012 at 12:19 am
This is normal. When the initial restore was done it was done WITH NORECOVERY. Another option is to restore the database WITH STANDBY, but you have to provide an UNDO file location when you do that. However, using WITH STANDBY will make the database avaialble for reads in between transaction log restores, a nice bonus in some environments.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 12:26 am
Ok thanks for replying. Looks like I need to choose another option to have the database in Standby / Read Only mode which is what the other secondary databases are on.
I will play around with these options.
May 15, 2012 at 10:13 am
I use something like this for mine.
RESTORE DATABASE [MYDB] FROM DISK = N'P:\data\Tdrive\full.bak' WITH FILE = 1, replace,
STANDBY = N'P:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\ROLLBACK_UNDO_MYDB.BAK', NOUNLOAD, STATS = 10
GO
Also if you get an incomplete log file shipped it will get stuck in the restoring mode untill you can throw a complete file at it.
May 15, 2012 at 1:45 pm
opc.three (5/15/2012)
This is normal. When the initial restore was done it was done WITH NORECOVERY. Another option is to restore the database WITH STANDBY, but you have to provide an UNDO file location when you do that. However, using WITH STANDBY will make the database avaialble for reads in between transaction log restores, a nice bonus in some environments.
What is that UNDO file location? What for it used ?
May 15, 2012 at 2:11 pm
jitendra.padhiyar (5/15/2012)
opc.three (5/15/2012)
This is normal. When the initial restore was done it was done WITH NORECOVERY. Another option is to restore the database WITH STANDBY, but you have to provide an UNDO file location when you do that. However, using WITH STANDBY will make the database avaialble for reads in between transaction log restores, a nice bonus in some environments.What is that UNDO file location?
The undo file location is the location where the undo file resides. I hope that clears things up. You can see the syntax in benjamin.reyes' earlier post where he specifies the location after STANDBY =.
What for it used ?
What the undo file is used for is another story. The undo file is needed during database recovery when restoring transaction logs from a primary database to a secondary database in standby mode. The undo file contains transactions that were in-flight, but not yet completed when the transaction log backup was taken on the primary. During the restore operation on the secondary database those incomplete transactions must be left un-applied to the secondary database in order to allow the secondary database to stay in a consistent state. Information about those incomplete transactions are stored in the undo file. The undo file is necessary because if the in-flight transactions were applied to the secondary database SQL Server would have to prevent access to the database in between log restores because the database is not considered transactionally complete. This is the difference between a database in the Standby/Read-only state and a database in the Restoring state.
The resolution of any of these incomplete transactions by way of a commit or rollback will be contained in a later log backup (not necessarily the next one though) coming from the primary to the secondary. When those subsequent log backups are applied to the secondary the undo file is again involved as it will contain the initial portion of those previously in-flight transactions.
It is a big topic. I hope I explained it well enough for you. Here is more information on the topic from Books Online article RESTORE Arguments:
STANDBY =standby_file_name
Specifies a standby file that allows the recovery effects to be undone. The STANDBY option is allowed for offline restore (including partial restore). The option is disallowed for online restore. Attempting to specify the STANDBY option for an online restore operation causes the restore operation to fail. STANDBY is also not allowed when a database upgrade is necessary.
The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file 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. After a RESTORE WITH STANDBY operation, the undo file is automatically deleted by the next RESTORE operation. If this standby file is manually deleted before the next RESTORE operation, then the entire database must be re-restored. While the database is in the STANDBY state, you should treat this standby file with the same care as any other database file. Unlike other database files, this file is only kept open by the Database Engine during active restore operations.
The standby_file_name specifies a standby file whose location is stored in the log of the database. If an existing file is using the specified name, the file is overwritten; otherwise, the Database Engine creates the file.
The size requirement of a given standby file depends on the volume of undo actions resulting from uncommitted transactions during the restore operation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 3:15 pm
Thanks a lot for the nice explanation!
One more question... Is undo file is like Checkpoint ?
Because according to my knowledge checkpoint also very usefull for database recovery, as it has information regarding the transaction which are uncommited. Is undo file creating checkpoint?
Please correct me if I am on the wrong path.
Thanks in advance.
May 15, 2012 at 3:59 pm
One more question... Is undo file is like Checkpoint ?
Maybe in an abstract way in that they both help us arrive at a transactionally consistent database in different scenarios, but they are not the same thing and cannot be thought of interchangeably.
Because according to my knowledge checkpoint also very usefull for database recovery, as it has information regarding the transaction which are uncommited.
A checkpoint is important when recovering a database. The MinLSN written at each checkpoint tells us the point in time where we can recover the database to a consistent state with respect to all committed and replicated transactions. I do not think replicated transactions are considered in the undo process since to my knowledge we cannot replicate from a secondary log shipping database. So, checkpoint only helps us know more information about the uncommitted transactions in so far as it helps us know to what point in the log we need to roll back when recovering a database to exclude them. The undo file actually stores information about uncommitted transactions allowing us to roll the uncommitted portion forward when applying the next log backup(s), and at some point the remaining parts of those transactions.
Is undo file creating checkpoint?
Not that I know of. The restore operation that makes use of the undo file gets the database into a transactionally consistent state where it can be set as read-only instead of restoring (inaccessible). So, in a sense, yes, in an abstract way, but they are not really the same thing.
I hope my understanding is satisfactory and that I have stated all the facts correctly. If it is incorrect I am confident corrections will be made by a more knowledgeable poster.
Checkpoints and the Active Portion of the Log
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 6:35 pm
Thanks opc.three!
I did restore the secondary database with the WITH NORECOVERY option yesterday which is why (now I realise) it is always in recovering mode since then. I have tried with the WITH STANDBY option and now it's in Standby / Read-Only mode like the other secondary databases.
I have no idea what the UNDO file location is for either until I read your posts today.
Thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply