SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Standby & NoRecovery


Standby & NoRecovery

Author
Message
lilywhites
lilywhites
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 910
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52497 Visits: 17672
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" ;-)
lilywhites
lilywhites
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 910
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
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2264 Visits: 979
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
lilywhites
lilywhites
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 910
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
lilywhites
lilywhites
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 910
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
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2264 Visits: 979
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
lilywhites
lilywhites
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 910
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 :-P
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2264 Visits: 979
Glad I could help. :-D

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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52497 Visits: 17672
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search