December 2, 2011 at 9:48 am
I've got logshipping set up between two SQL 2008 EE (64-bit) servers and it's been running smoothly for quite some time...until this morning at about 1:45am when the primary database ran out of space in the LDF file...
Then this error started:
Message
2011-12-02 01:52:26.08*** Error: Could not apply log backup file '\\xxx-data1\Backups\sql_backups\LOGSHIPPING\COREXXX\COREXXXE_20111202070001.trn' to secondary database 'COREXXX'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-12-02 01:52:26.08*** Error: The transaction log for database 'COREXXX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'COREXXX', file 'mr_coreissue' on file 1.
Processed 959864 pages for database 'COREXXX', file 'mr_coreissue_log' on file 1.
Processed 678793 pages for database 'COREXXX', file 'COREXXX_Log' on file 1.(.Net SqlClient Data Provider) ***
With the current configuration, the secondary databases are set up in STANDBY mode...and currently the database referenced above is showing that it's stuck in restoring mode.
How can I get into the DB so I can increase the LDF file size? I was under the impression that when I increased the size of the LDF file on the primary server that it would have pushed it over to the secondary. Any thoughts on how I can fix this?
My only thought is to restore the last successful TRN file as RESTORE WITH RECOVERY, change the size of the LDF file, then continue restoring the next TRN files using WITH STANDBY until I'm back up to where I need to be...but even when atempting to start that process I get an error "The selected database, COREISSUE, cannot be restored in its current state. (SqlManagerUI)"
?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 9:59 am
is there the space on the secondary log file drive for the log to increase to the size you increased it to on the primary?
If you do recover the secondary database you will not be able to restore any more log files and will have to restart from a full backup.
Restoring from a full backup may be your best way out of this issue anyway.
---------------------------------------------------------------------
December 2, 2011 at 10:03 am
Yes there is ample space.
That's what I was afraid of...having to restore from a full backup and basically starting over with logshipping from that database...can I restore just the last differntial back, then restore the TRN files since that backup?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 10:10 am
yes you could, but check out log_reuse_wait_desc column in sys.databases first as you have log full errors.
---------------------------------------------------------------------
December 2, 2011 at 10:15 am
MyDoggieJessie (12/2/2011)
Yes there is ample space.That's what I was afraid of...having to restore from a full backup and basically starting over with logshipping from that database...can I restore just the last differntial back, then restore the TRN files since that backup?
I think it can be done (at least we should attempt) however BOL says following.
"The secondary database must be initialized by restoring a full backup of the primary database. The restore can be completed using either the NORECOVERY or STANDBY option."
December 2, 2011 at 10:18 am
MyDoggieJessie (12/2/2011)
...can I restore just the last differntial back, then restore the TRN files since that backup?
No, once you recover the database no more backups (differential or log) can be applied. To restart the log shipping you'd need to restore a full backup either WITH NORECOVERY or WITH STANDBY and then the logs can restore to that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 10:24 am
GilaMonster (12/2/2011)
MyDoggieJessie (12/2/2011)
...can I restore just the last differntial back, then restore the TRN files since that backup?No, once you recover the database no more backups (differential or log) can be applied. To restart the log shipping you'd need to restore a full backup either WITH NORECOVERY or WITH STANDBY and then the logs can restore to that.
But we are just planning for restore not recovery...
December 2, 2011 at 10:30 am
...took for granted the differential restore would be with norecovery and you haven't previously bought the database online (used with recovery anywhere)
---------------------------------------------------------------------
December 2, 2011 at 10:32 am
Dev (12/2/2011)
GilaMonster (12/2/2011)
MyDoggieJessie (12/2/2011)
...can I restore just the last differntial back, then restore the TRN files since that backup?No, once you recover the database no more backups (differential or log) can be applied. To restart the log shipping you'd need to restore a full backup either WITH NORECOVERY or WITH STANDBY and then the logs can restore to that.
But we are just planning for restore not recovery...
My only thought is to restore the last successful TRN file as RESTORE WITH RECOVERY,
hence recovering the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 10:36 am
GilaMonster (12/2/2011)
Dev (12/2/2011)
GilaMonster (12/2/2011)
MyDoggieJessie (12/2/2011)
...can I restore just the last differntial back, then restore the TRN files since that backup?No, once you recover the database no more backups (differential or log) can be applied. To restart the log shipping you'd need to restore a full backup either WITH NORECOVERY or WITH STANDBY and then the logs can restore to that.
But we are just planning for restore not recovery...
My only thought is to restore the last successful TRN file as RESTORE WITH RECOVERY,
hence recovering the database.
I am fine as long as it's just a thought... Not implemented yet... as George said 'took for granted the differential restore would be with norecovery'
December 2, 2011 at 10:40 am
Thanks for the replies everyone...this is what I thought :crazy:
So I'll have to copy up and then restore the latest full backup to the secondary server (which will take about 18 hours to complete), then copy up the differential backups that have occurred since that full backup, then reconfigue the log shipping altogether for this database. Should I disable the full backup from running again? What I mean is if I'm going to restore this current full backup, then restore the sequential differential backups, if that time overlaps with the next full backup on the primary server....won't that break the LSN chain from the get-go? or will I be okay?
Thoughts?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 10:43 am
MyDoggieJessie (12/2/2011)
What I mean is if I'm going to restore this current full backup, then restore the sequential differential backups, if that time overlaps with the next full backup on the primary server....won't that break the LSN chain from the get-go? or will I be okay?
You only need to restore the last Differential backup, not all of them. So it'll be full backup, latest differential backup, then the log backups since the last differential.
Full backups don't ever break the log chain, they only reset the differential base.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 10:43 am
One more thought: while I am redoing this for the broken DB, should I blow away the current log shipping setup for the database? If I don't the TRN files will still be created/copied to my network log shipping directory...and if I do blow away the existing log shipping for the DB, I'll need to add TLOG backups to my existing maintenance plans to avoid my primary DB log files from filling up. Is this correct?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 10:46 am
GilaMonster (12/2/2011)
MyDoggieJessie (12/2/2011)
What I mean is if I'm going to restore this current full backup, then restore the sequential differential backups, if that time overlaps with the next full backup on the primary server....won't that break the LSN chain from the get-go? or will I be okay?You only need to restore the last Differential backup, not all of them. So it'll be full backup, latest differential backup, then the log backups since the last differential.
Thanks Gail, I will follow this advice and take it from there...(crossing my fingers)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 11:31 am
p.s. there's no point in recovering the database, shrinking the log and then restoring a full backup over it. The restore will wipe out the existing DB and replace it with a DB exactly like on the primary.
So, you may as well just restore the full over the existing DB as step 1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply