January 4, 2011 at 12:47 pm
Hi,
I just encountered a problem in log shipping. It got out of sync for a long duration.
So, I wanted to re establish the log shippping as it was very hard to find out hte actual cause of breaking the logs.
I first deleted the database from the secondary side and then tried to re-establish the log shipping on the primary side from scratch ( with a new full backup and choosing the option of creating the db if it doesn't exist.)
After the full backup was done. I got the error of less disk space at the secondary side, where the db was earlier residing. I have the same space as it was earlier ( at time of log shipping going out of sync). I guess it(space) needs 1.5 times the size of a db, when we want to add a new db and restore it at the same time. I dont have any more free drives to add up.
So, what I did is, I made a db of same name and size on secondary side as on primary side and created it as empty. Now I changed it into read only mode (as log shipping criterion).
Now, can I try the same re-establishing the log shipping at the primary side now.
Will there be restoration in read only mode?
or will the less space error message again pop up.
I want to know if, I can do that and will it be same as a normal log shipping db again?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 4, 2011 at 12:52 pm
There's no point in creating a database before you do the restore. Whatever that database is is completely overwritten during the restore, no properties, structure or settings remain. Just restore the backup with no_recovery or standby without creating a database first.
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
January 5, 2011 at 6:10 am
Hi,
I was able to successfully restore the database on secondary side and also set up log shipping.
But there was a variance.
As far as I know, log shipping is set up nicely as on the secondary server, I can see the copy and restore jobs working fine and also, when I see the transaction log shipping status on secondary, it shows that database is in sync.
BUT, when i see the transaction log shipping status on primary, it shows : -
Error:- Violation of PRIMARY KEY constraint 'PK_#log_shipping_mo_7A295A9E'. Cannot insert dupicate key in object 'dbo.#log_shipping_monitor'.
The statment has been terminated.
Also, the LS_Alert job on primary side is failing. (Its code (step) is
exec sys.sp_check_log_shipping_monitor_alert ).
When, i check the backup jobs on primary and the copy and restore jobs on secondary, everythign seems fine and I strongly think that database is in sync.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 6, 2011 at 11:55 am
SKYBVI (1/5/2011)
Hi,I was able to successfully restore the database on secondary side and also set up log shipping.
But there was a variance.
As far as I know, log shipping is set up nicely as on the secondary server, I can see the copy and restore jobs working fine and also, when I see the transaction log shipping status on secondary, it shows that database is in sync.
BUT, when i see the transaction log shipping status on primary, it shows : -
Error:- Violation of PRIMARY KEY constraint 'PK_#log_shipping_mo_7A295A9E'. Cannot insert dupicate key in object 'dbo.#log_shipping_monitor'.
The statment has been terminated.
Also, the LS_Alert job on primary side is failing. (Its code (step) is
exec sys.sp_check_log_shipping_monitor_alert ).
When, i check the backup jobs on primary and the copy and restore jobs on secondary, everythign seems fine and I strongly think that database is in sync.
Regards,
Sushant
This indicates that the log shipping is not removed cleanly.
Check the logshipping tables in msdb databases and delete the db entry that is not being logshipped and then resetup logshipping if required (there might be orphaned entries in the msdb logshipping tables resulting in failure of LS_Alert job):
on Primary:
log_shipping_monitor_secondary
log_shipping_secondary
On secondary:
log_shipping_monitor_primary
log_shipping_primary_secondaries
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 6, 2011 at 11:55 am
SKYBVI (1/5/2011)
Hi,I was able to successfully restore the database on secondary side and also set up log shipping.
But there was a variance.
As far as I know, log shipping is set up nicely as on the secondary server, I can see the copy and restore jobs working fine and also, when I see the transaction log shipping status on secondary, it shows that database is in sync.
BUT, when i see the transaction log shipping status on primary, it shows : -
Error:- Violation of PRIMARY KEY constraint 'PK_#log_shipping_mo_7A295A9E'. Cannot insert dupicate key in object 'dbo.#log_shipping_monitor'.
The statment has been terminated.
Also, the LS_Alert job on primary side is failing. (Its code (step) is
exec sys.sp_check_log_shipping_monitor_alert ).
When, i check the backup jobs on primary and the copy and restore jobs on secondary, everythign seems fine and I strongly think that database is in sync.
Regards,
Sushant
This indicates that the log shipping is not removed cleanly.
Check the logshipping tables in msdb databases and delete the db entry that is not being logshipped and then resetup logshipping if required (there might be orphaned entries in the msdb logshipping tables resulting in failure of LS_Alert job):
on Primary:
log_shipping_monitor_secondary
log_shipping_secondary
On secondary:
log_shipping_monitor_primary
log_shipping_primary_secondaries
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 6, 2011 at 12:04 pm
Is your issue resolved?
January 10, 2011 at 6:04 am
@ SQL_Quest
Thanks, it worked.
I had some stale data in the log shipping monitor tables on primary side.
I deleted it and all log shipping was back in sync.
Thanks all of you.
Regards,
Sushant
DBA
West Indies.
Regards
Sushant Kumar
MCTS,MCP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply