July 9, 2012 at 7:27 am
Hi All,
I have implemented Transactional Log Shipping on a test server for the first time. The backup, copy and restore job are all working well.
The transactional backups are scheduled for evry 1 hr. And they are also scheduled to be deleted after 180 mins. This works fine.
However , the files that get copied to the secondary server are not being deleted , inspite of enabling the option : delete files after 180 mins.
Could someone please help me out here ?
July 9, 2012 at 3:18 pm
According to my knowledge, they will not be deleted on secondry server, it will be deleted on Primary server.
July 10, 2012 at 12:45 am
Run this on your secondary instance, replacing the DB name with your own, and refer to the file_retention_period column in the results.
USE master;
EXEC sys.sp_help_log_shipping_secondary_database
@secondary_database = 'YourDatabaseName';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 10, 2012 at 4:31 am
Ran the above query . It shows the retention period as 180 .. which i guess refers to the 180 minutes set by me.
Yet the secondary transactional backup files arent deleted.
July 10, 2012 at 4:54 am
jitendra.padhiyar (7/9/2012)
According to my knowledge, they will not be deleted on secondry server, it will be deleted on Primary server.
Au contrair mon pere 😉
sufiyan.sarguroh (7/10/2012)
Ran the above query . It shows the retention period as 180 .. which i guess refers to the 180 minutes set by me.Yet the secondary transactional backup files arent deleted.
yes it is indeed the number of minutes. Check the logs for any potential errors
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2012 at 8:11 am
Checked the logs. It appears that the tran backups arent being restored.
Here is the error in the log :
The log shipping secondary database INFVA02839\INSTANCE1.AdventureWorks has restore threshold of 120 minutes and is out of sync. No restore was performed for 27290 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
What was suprising is that the Restore Job is showing that it was run successfully in the job history.
But something surely is going wrong.
So i checked the table - log_shipping_monitor_history_detail
And it seems no logs are being restored 🙁 .
Heres what it says :
1.
Skipped log backup file. Secondary DB: 'AdventureWorks', File: 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log_ship_secondary\AdventureWorks_20120710103022.trn'
2.
Could not find a log backup file that could be applied to secondary database 'AdventureWorks'.
3.
Skipped log backup file. Secondary DB: 'AdventureWorks', File: 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log_ship_secondary\AdventureWorks_20120710113021.trn'
4.
The restore operation was successful. Secondary Database: 'AdventureWorks', Number of log backup files restored: 0
Could someone please help me resolve this issue.
July 10, 2012 at 8:28 am
sufiyan.sarguroh (7/10/2012)
Checked the logs. It appears that the tran backups arent being restored.Here is the error in the log :
The log shipping secondary database INFVA02839\INSTANCE1.AdventureWorks has restore threshold of 120 minutes and is out of sync. No restore was performed for 27290 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
😉 exactly what i expected you would see.
sufiyan.sarguroh (7/10/2012)
What was suprising is that the Restore Job is showing that it was run successfully in the job history.
Yes, it does, annoying that isn't it.
I'm guessing that the backups on the primary have long since been pruned and what the secondary database requires is an earlier backup restoring before the current files can be restored.
It's most likely that a log backup has been taken outside of the log shipping scenario, only the log shipping backup job should be taking backups of the log shipped primary database.
Disable the 3 log shipping jobs and take a differential backup of the primary and apply it to the secondary. Clean all backup files from the secondary location, then re enable the 3 jobs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 3:25 am
Is there a reason you set a retention period of only 180 minutes? It doesn't give you a lot of leeway if the connection between the primary and secondary servers is disrupted for any reason--I usually keep my log backups for at least 3 days, and have the secondary server e-mail me if it goes out of sync for any reason so I can immediately start troubleshooting.
July 11, 2012 at 6:11 am
paul.knibbs (7/11/2012)
Is there a reason you set a retention period of only 180 minutes? It doesn't give you a lot of leeway if the connection between the primary and secondary servers is disrupted for any reason--I usually keep my log backups for at least 3 days, and have the secondary server e-mail me if it goes out of sync for any reason so I can immediately start troubleshooting.
The only reason was that since it was a test server i wanted to see the results soon. Dint want to wait for 3 days 😀
I'm guessing that the backups on the primary have long since been pruned and what the secondary database requires is an earlier backup restoring before the current files can be restored.
It's most likely that a log backup has been taken outside of the log shipping scenario, only the log shipping backup job should be taking backups of the log shipped primary database.
Disable the 3 log shipping jobs and take a differential backup of the primary and apply it to the secondary. Clean all backup files from the secondary location, then re enable the 3 jobs
Quite Right. It worked perfectly fine now.
So , just to summarise , it was because a log backup had been taken outside of the job.
Due to which the later log backus couldnt be restored on the secondary , right ? Something to do with LSN perhaps .
And yes, the backups are getting deleted now !
Thanks a lot Perry 🙂
July 11, 2012 at 7:21 am
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply