Standby & NoRecovery

  • Hello,

    We have a standby database, which is a manual, log-shipped copy of live, which we report and query against. This is kept 24hrs behind live and a job runs to restore all transaction logs transferred from live overnight.

    The job runs code similar to the following:

    RESTORE LOG <<DBName>>

    FROM DISK = N'<<filename>>.trn'

    WITH FILE = 1, STANDBY = N'<<filename>>.stdby'

    GO

    RESTORE LOG <<DBName>>

    FROM DISK = N'<<filename>>.trn'

    WITH FILE = 1, STANDBY = N'<<filename>>.stdby'

    GO

    etc, etc....................

    recently we have been having connections getting through in-between the log restores and causing issues. rather than messing with triggers on logons and other processes i was wondering if we could alter the restore logic to not use the standby file until the last log restore and replace it with norecovery....so the code will be similar to:

    etc, etc....

    RESTORE LOG <<DBName>>

    FROM DISK = N'<<filename>>.trn'

    WITH FILE = 1, NORECOVERY'

    GO

    RESTORE LOG <<DBName>>

    FROM DISK = N'<<filename>>.trn'

    WITH FILE = 1, STANDBY = N'<<filename>>.stdby'

    GO

    This will avoid any connections in-between the log restores and should potentially resolve the uncommitted transactions from the final restore....just unsure that if there are any uncommitted transactions that span multiple log restores (which there shouldnt be....but you never know) how it will handle them without standby information for them.

    Many thanks

    Samuel

  • lilywhites (4/22/2014)


    recently we have been having connections getting through in-between the log restores and causing issues.

    What issues have you been experiencing?

    Can you post the output of the following query run against the secondary server

    EXEC sp_help_log_shipping_secondary_database

    @secondary_database = 'Your_secondary_database'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • i cannot....its a "manual" log shipping process controlled by a windows task and using powershell.

    during the night when there is a string of log restores we are getting the following error logged in the restore log:

    S:\Replication\Files\IM_Money\TRANSACTIONS\IM_Money_backup_2014_04_19_110000.trn

    Processed 0 pages for database 'IM_Money_Analysis', file 'IM_Money' on file 1.

    Processed 0 pages for database 'IM_Money_Analysis', file 'IM_Money_SYSINTERNAL' on file 1.

    Processed 0 pages for database 'IM_Money_Analysis', file 'IM_Money_YODLEETXNS' on file 1.

    Processed 246415 pages for database 'IM_Money_Analysis', file 'IM_Money_log' on file 1.

    RESTORE LOG successfully processed 246415 pages in 108.151 seconds (17.800 MB/sec).

    S:\Replication\Files\IM_Money\TRANSACTIONS\IM_Money_backup_2014_04_19_120000.trn

    Msg 3101, Level 16, State 1, Server TSUNAMI, Line 2

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Server TSUNAMI, Line 2

    RESTORE LOG is terminating abnormally.

    So in a chain of restores we get successful, successful, failure!

    At that point we dont have the logic in place to handle this and wait.

    We cannot use SQL log shipping as there is no trusted link between the two domains due to security (financial).

    We start the restore with the command:

    ALTER DATABASE IM_Money_Analysis SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Each restore in the chain is running using the STANDBY file....i was wondering if we could remove these STANDBY commands and just use it at the end of the chain?? The only other alternative is to repeat the SET SINGLE_USER WITH ROLLBACK IMMEDIATE command before each restore??

    thanks

  • If you are setting the database to single user how is there connections getting through? I would set the database to single user before each restore and see if that helps.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • i am assuming that when using a standby file it sets the database to standby after each restore and the SINGLE_USER may not apply from that point onwards

    ill test....thanks

  • im still wondering if there are any risks with only applying the standby file to the final log restore in the chain....this will also speed up the restores which isnt a bad thing??

    thanks

  • That would work too if you didn't want to allow access to the database during the restore. So basically you would restore all Tlog backups with NORECOVERY until the last backup and restore with STANDBY. It would speed up the restore process too.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • aahhhhh....perfect!!

    thats what i was hoping for....been trying to develop a restore plan to test the theory but was struggling!!

    i have put in the SINGLE_USER command before each log restore for now and will fully test the NORECOVERY change and implement ASAP!! and in a small test my backup chain progressed up to 50% quicker without all the standby commands.

    thank you very much!!

    give that man some points 😛

  • Glad I could help. 😀

    I tested it out with one of my databases and it worked fine, so you should be good to go.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • lilywhites (4/22/2014)


    Each restore in the chain is running using the STANDBY file....i was wondering if we could remove these STANDBY commands and just use it at the end of the chain?? The only other alternative is to repeat the SET SINGLE_USER WITH ROLLBACK IMMEDIATE command before each restore??

    thanks

    In this case, once the database mode has been altered to SINGLE_USER, just run each restore with the norecovery option and the last with standby. Note that all restores will need to be done through the same session when using SINGLE_USER.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply