Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Standby & NoRecovery Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 2:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:56 AM
Points: 90, Visits: 712
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
Post #1563723
Posted Tuesday, April 22, 2014 5:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1563776
Posted Tuesday, April 22, 2014 6:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:56 AM
Points: 90, Visits: 712
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
Post #1563826
Posted Tuesday, April 22, 2014 7:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 588, Visits: 901
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
Post #1563842
Posted Tuesday, April 22, 2014 7:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:56 AM
Points: 90, Visits: 712
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
Post #1563850
Posted Tuesday, April 22, 2014 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:56 AM
Points: 90, Visits: 712
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
Post #1563854
Posted Tuesday, April 22, 2014 9:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 588, Visits: 901
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
Post #1563891
Posted Tuesday, April 22, 2014 9:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:56 AM
Points: 90, Visits: 712
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
Post #1563894
Posted Tuesday, April 22, 2014 9:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 588, Visits: 901
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
Post #1563902
Posted Tuesday, April 22, 2014 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1563953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse