August 11, 2011 at 5:54 pm
Anyone know why my sql server 2008 R2 log shipping retention is removing files that are older than 3 days and not 6? I went through the settings wizard and stipulated 6 days. Also, log_shipping_primary_databases.backup_retention_period field shows 6 days (8640 minutes). Any suggestions would be greatly appreciated!
August 11, 2011 at 7:05 pm
are you sure it is sql server doing it?
Is there by chance another process on the server (or somewhere) that is removing files older than 3 days?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2011 at 9:24 pm
Like a maintenance task set to delete old tran log backups older than 3 days 🙂
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 12, 2011 at 9:48 am
No other jobs to delete my transaction log backup files. I do have a job to delete my Full backup files after 2 days. However, those are a different drive and folder. That shouldn't conflict with this right? Also, a correction to my first post, my transaction log files are deleting after only 2 days not 3 like I said earlier, so maybe there is a connection?
August 12, 2011 at 9:52 am
That maint job to remove backup files sounds like a good candidate to me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 10:01 am
any idea's why it would remove my log shipping logs too? How would it know where to remove them from when it's poing to a different folder than my log backups? or is this something that sql does with backups and just removes them all that are listed in the backup set history or something. I'm not that familiar with how backup sets are handled in sql server
August 12, 2011 at 10:20 am
Have you checked the times on the servers? Make sure they're the same? I might look for some timing differences that cause things to delete. Even a few minutes over 3 days might cause a delete, even though it seems like 2 days to you.
August 12, 2011 at 10:27 am
The server times are the same. Also, the full backup is scheduled for 11pm. However, the logs are getting deleted after exactly 2 days. For example, the logs are generated every 15 minutes. so at 9:30am I will have logs that only go back to 9:30am 2 days ago. In other words, they are getting deleted every 15 minutes. It seems to me sql server just isn't respecting my recent change in the log shipping retention period to 6 days. Is there some way to force it to recognize this change some how? I would rather not have to restart the sql service or server as it's production, but do you think that would fix it?
August 12, 2011 at 11:26 am
I wouldn't think log shipping would require a restart. If you check the log shipping tables, are your settings changed?
August 12, 2011 at 11:42 am
Yeah, my first post shows the field... is there another place somewhere? Also, I did use the wizard to set this. Should it be ok to just change this value manual or would I have to use the wizard or one of the system update sp's?
August 12, 2011 at 12:41 pm
Sorry, didn't realize you're posted that. Just so we're clear, you have log shipping set up on a primary and secondary server. The primary server (source) has logs set to retain at 6 days, but they're being deleted on the primary after 3 days.
Are they being removed from the secondary in a similar time frame and at the correct time frame?
I hate to suggest a reboot, though that might be needed.
Two other things, have you tried a "reconfigure with override"? I know it's not supposed to apply here, but just thinking. Before you do, check sp_configure and see if any settings are out of whack. I wouldn't want something else to change you weren't expecting. Compare configured v running values.
The other thing, can you try using Sp_change_log_shipping_primary_database to reset this again?
August 14, 2011 at 3:34 am
jprovines
please execute the following against the primary server
use master
exec sp_help_log_shipping_primary_database @database = 'yourdb'
and this against the secondary
use master
exec sp_help_log_shipping_secondary_database @secondary_database = 'yourdb'
Post the results for both these!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2011 at 9:13 am
Here is the information requested. However, note the secondary database retention time wasn't increased because I haven't set that yet, this shouldn't affect the first location retention period tho right? Also, the first database has a mirror and we haven't yet increased the mirror's retention period either. We also have a monitor server if that matters.
primary_idB4B74BA0-BBF0-4401-AECC-D1C7E756937A
primary_databaseMainDb
backup_directory\\lap8db03\TransactionLogBackup
backup_share\\lap8db03\TransactionLogBackup
backup_retention_period8640
backup_compression2
backup_job_id0D6CD538-40C9-427F-8625-B15910A8E2C5
monitor_serverLAP8DB03
monitor_server_security_mode1
backup_threshold60
threshold_alert14420
threshold_alert_enabled1
last_backup_file\\lap8db03\TransactionLogBackup\MainDb_20110815150000.trn
last_backup_date00:01.5
last_backup_date_utc00:01.5
history_retention_period5760
-------------------------------------------------------------------------
secondary_id1366DE16-00A5-4155-A814-0661786DB3B2
primary_serverLAP8EA01
primary_databaseMainDb
backup_source_directory\\lap8db03\TransactionLogBackup
backup_destination_directory\\lap8ea03\TransactionLogShippingTemp
file_retention_period2880
copy_job_id15866471-EA92-4EC7-BE88-068A4DA8149E
restore_job_idC868B626-AE85-41A8-B6A7-73EBBFF341F4
monitor_serverLAP8DB03
monitor_server_security_mode1
secondary_databaseMainDb
restore_delay0
restore_all1
restore_mode1
disconnect_users1
block_size-1
buffer_count-1
max_transfer_size-1
restore_threshold45
threshold_alert14421
threshold_alert_enabled1
last_copied_file\\lap8ea03\TransactionLogShippingTemp\MainDb_20110815150000.trn
last_copied_date06:05.5
last_copied_date_utc06:05.5
last_restored_file\\lap8ea03\TransactionLogShippingTemp\MainDb_20110815144500.trn
last_restored_date00:06.8
last_restored_date_utc00:06.8
history_retention_period5760
last_restored_latency15
August 15, 2011 at 10:44 am
jprovines (8/15/2011)
backup_source_directory\\lap8db03\TransactionLogBackupbackup_destination_directory\\lap8ea03\TransactionLogShippingTemp
file_retention_period2880
initially everything seems ok, this value
file_retention_period2880
will apply to this
backup_destination_directory\\lap8ea03\TransactionLogShippingTemp
and not this
backup_source_directory\\lap8db03\TransactionLogBackup
The secondary server should never delete files from the source backup direrctory as there could be more than one secondary server using them, it should only delete from its local store\folder.
Are we 110% certain that nothing is pruning files from this location
\\lap8db03\TransactionLogBackup
It might be worth turning on auditing for that folder and checking object access to see what is going on.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2011 at 3:24 pm
111% sure...
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply