July 10, 2012 at 9:29 am
Hello Masters,
Yesterday, I configured a test log shipping with trial version of SQL server 2008,today i found there were no backup resotre from last 1055 minutes, i could not find why it was not restored. Today again I reconfigured the same and found that backup and copy job is working fine but resotre job is not working.
I dont know how to check why its not working and what was the last backup set restored.
Please help me to find out.
Thanks in advance.
July 10, 2012 at 9:51 am
ensure you are not taking log backups outside of your log shipping plan, only the log shipping backup job should be taking log backups of the database. Also ensure the retention period on the primary server is sufficient to allow successful copy operations to the secondary
Please post results of the following 2 queries
against the primary
exec sp_help_log_shipping_primary_database
@database = 'yourdatabase'
Against the secondary
exec sp_help_log_shipping_secondary_database
@secondary_database = 'yourdatabase'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2012 at 11:35 am
Thanks for the reply, below are the result of both the queries:
Please post results of the following 2 queries
against the primary
exec sp_help_log_shipping_primary_database
@database = 'yourdatabase'
Result:
Primary_id=548492AD-0BDB-4A2E-B31E-BD32BDED52CF
Primary_database=AdventureWorksDW
Backup_directory=D\backup_ls
Backup_share=\\192.10.10.1\Backup_ls
Backup_retention_period=30
Backup_compression=2
Backup_job_id=
monitor_server=Null
Monitor_Server_security_mode= Null
Backup_threshold=20
Threshold_alert=14420
Threshold_alert_enabled=1
last_backup_file=D:\backup_ls\AdventureWorksDW_2012071071012.trn
Last_backup_date=2012-07-10
History_retention_period=5760
Against the secondary
exec sp_help_log_shipping_secondary_database
@secondary_database = 'yourdatabase'
Result:
Secondary_id=D737BE9C-2500-4B51-9CE1-B36E56846A4C
Primary_server=JPTEST\SQLLOCAL
Primary_database=AdventureWorksDW
Backup_source_directory=\\192.10.10.1\Backup_Ls
Backup_destination_directory=E:\Restore
File_retention_period=30
Copy_job_id=571602CF-1CCF-430C-LAKDJ345-AKDFK12
Restore_job_id= C3B9AA9A-C3AD-A411-9477-D7654423
Monitor_server=NULL
Monitor_server_security_mode-Null
Secondary_database=AdventureWorksDW
Restore_delay=1
Restore_all=1
Restore_mode=1
Disconnect_users=0
block_size= -1
buffer_count= -1
max_transfer_size= -1
restore_threshold=45
threshold_alert=14421
threshold_alert_enabled=1
last_copied_file=E:\Restore\AdventureWorksDW_2012071017512.trn
Last_copied_date= 2012-07-10 22:45:15
Last_restored_file=Null
Last_resotred_date=2012-07-10 19:49:15
history_retention_period=5760
Last_restored_latency=Null
July 10, 2012 at 1:04 pm
Firstly, I'm assuming this is a typo!!
jitendra.padhiyar (7/10/2012)Backup_directory=D\backup_ls
jitendra.padhiyar (7/10/2012)Backup_retention_period=30
30 minutes is a little aggressive for the file pruning. How often does the backup job, copy job and restore job run?
Full details of their schedules please.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2012 at 1:38 pm
Below are the details that I setup for testing:
Backup job = Every 5 min.
Copy Job = Every 5 min.
Restore Job = Every 7 min.
Delete files older than = 30 min.
Delete copied files after = 30 min.
July 10, 2012 at 2:06 pm
Have you looked for errors on the log shipping monitor or secondary server?
SELECT TOP 1000 [agent_id]
,[agent_type]
,[session_id]
,[database_name]
,[session_status]
,[log_time]
,[log_time_utc]
,[message]
FROM [msdb].[dbo].[log_shipping_monitor_history_detail]
July 10, 2012 at 2:36 pm
I can see on message column, "skipped log file" and "searching through log backup to restore". According to me this indicates that some transaction files are missing and thats why its not restoring anything.
correct me if i am wrong.
If so, i have to reconfigure log shipping.
July 10, 2012 at 2:57 pm
Make sure that you grab the most recent restore records from that query first. If indeed they are showing that the most recent file has nothing to restore then confirm your copy job is working fine by comparing the filename being checked against the newest file. Then check that you've actually posted transactions on your primary.
July 10, 2012 at 4:58 pm
Nicholas Cain (7/10/2012)
Have you looked for errors on the log shipping monitor
There is no monitor server, the return value for this in sp help log shipping primary database was null.
jitendra.padhiyar (7/10/2012)
I can see on message column, "skipped log file" and "searching through log backup to restore". According to me this indicates that some transaction files are missing and thats why its not restoring anything.correct me if i am wrong.
If so, i have to reconfigure log shipping.
It's skipping files likely because it's looking for a backup with an earlier LSN to restore. First thing is to find out what is interferring with the LS setup. Do you have any other back jobs or maintenance plans on the server?
Once you found the culprit and have it sorted you need to bridge the LSN gap.
Disable the 3 LS jobs and then take a differential backup which you will then restore to the secondary db.
Re enable the 3 jobs and check that files are being copied and restored correctly.
As I said I think you are being a little too aggressive with your retention settings, but hey only you can decide this.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 8:11 am
Is there any query or any DBCC or any DMV check to find out which transaction log backup last restored?
July 11, 2012 at 8:21 am
jitendra.padhiyar (7/11/2012)
Is there any query or any DBCC or any DMV check to find out which transaction log backup last restored?
exec sp_help_log_shipping_secondary_database
@secondary_database = 'yourdatabase'
jitendra.padhiyar (7/11/2012)
Result:Secondary_id=D737BE9C-2500-4B51-9CE1-B36E56846A4C
Primary_server=JPTEST\SQLLOCAL
Primary_database=AdventureWorksDW
Backup_source_directory=\\192.10.10.1\Backup_Ls
Backup_destination_directory=E:\Restore
File_retention_period=30
Copy_job_id=571602CF-1CCF-430C-LAKDJ345-AKDFK12
Restore_job_id= C3B9AA9A-C3AD-A411-9477-D7654423
Monitor_server=NULL
Monitor_server_security_mode-Null
Secondary_database=AdventureWorksDW
Restore_delay=1
Restore_all=1
Restore_mode=1
Disconnect_users=0
block_size= -1
buffer_count= -1
max_transfer_size= -1
restore_threshold=45
threshold_alert=14421
threshold_alert_enabled=1
last_copied_file=E:\Restore\AdventureWorksDW_2012071017512.trn
Last_copied_date= 2012-07-10 22:45:15
Last_restored_file=Null
Last_resotred_date=2012-07-10 19:49:15
history_retention_period=5760
Last_restored_latency=Null
😎 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 9:48 am
Thanks Perry for the quick reply,
exec sp_help_log_shipping_secondary_database
@secondary_database = 'yourdatabase'
Above query can be used only for database of log shipping. If I dont have log shipping, than how I can know the last backup set that has been restored?
July 12, 2012 at 1:07 am
Sorry I'm confused I thought we were discussing log shipping!!
The backup and restore information is held in MSDB, query the tables here for this info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 12, 2012 at 8:24 am
Yes, the given query for log shipping is very much useful. But I am asking in general that if I want to know that which backup set was last restored, than how can I know that ?
July 12, 2012 at 9:22 am
Run the following queries to find which file was the last one to be copied and which file was the last one to be restored.
The following query will give you the last log backup file that was copied
SELECT * FROM [msdb].[dbo].[log_shipping_secondary]
Then check what was the last log backup file that was restored.
SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]
Blog
http://saveadba.blogspot.com/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply