October 10, 2012 at 11:02 am
My log shipping was working for the past two years and now it has started failing with "cannot find a file that can be applied" or "Too recent" errors.......
Only change i remember doing is enabling the compression for full backups, but i am not sure if that has caused the issue.
I checked the backupset table and i see unkown enteries after my full backup and before the TLog backups, which explains why my TLog is failing. But i have no idea where these enteries are coming from, we don't have any other backup job inplace.
Here is what i see in the backupset table (also attached the image).
backup_set_idnametypebackup_sizeUser_Namebackup_start_datebackup_finish_date
297558NULLL2605056NT AUTHORITY\SYSTEM2012-10-10 05:00:02.0002012-10-10 05:00:02.000
297550NULLL1173440512NT AUTHORITY\SYSTEM2012-10-10 04:58:58.0002012-10-10 04:59:09.000
297534NULLD39302793728NT AUTHORITY\SYSTEM2012-10-10 01:36:47.0002012-10-10 01:36:54.000
297529CRM_backup_2012_10_09_210004_7445550D39126608896Domain\SQLSrvcAct2012-10-09 21:19:06.0002012-10-09 21:50:57.000
Id "297529" is through our maintenance plan, but i don't know where the other three came from, note that name is null for other three and user is NT AUTHORITY\SYSTEM.
October 10, 2012 at 11:16 am
Please answer the below questions
1.When did log shipping starts failing?
2.Is any associated logs in SQL Server Error log?If yes,send that
3.When did you change 'enabling the compression for full backup'?
Regards,
Kumar
October 11, 2012 at 3:24 am
Lazy DBA (10/10/2012)
My log shipping was working for the past two years and now it has started failing with "cannot find a file that can be applied" or "Too recent" errors.......Only change i remember doing is enabling the compression for full backups, but i am not sure if that has caused the issue.
I checked the backupset table and i see unkown enteries after my full backup and before the TLog backups, which explains why my TLog is failing. But i have no idea where these enteries are coming from, we don't have any other backup job inplace.
Here is what i see in the backupset table (also attached the image).
backup_set_idnametypebackup_sizeUser_Namebackup_start_datebackup_finish_date
297558NULLL2605056NT AUTHORITY\SYSTEM2012-10-10 05:00:02.0002012-10-10 05:00:02.000
297550NULLL1173440512NT AUTHORITY\SYSTEM2012-10-10 04:58:58.0002012-10-10 04:59:09.000
297534NULLD39302793728NT AUTHORITY\SYSTEM2012-10-10 01:36:47.0002012-10-10 01:36:54.000
297529CRM_backup_2012_10_09_210004_7445550D39126608896Domain\SQLSrvcAct2012-10-09 21:19:06.0002012-10-09 21:50:57.000
Id "297529" is through our maintenance plan, but i don't know where the other three came from, note that name is null for other three and user is NT AUTHORITY\SYSTEM.
How often do full backups run?
What are the results of the following queries executed against the primary and secondary instances?
Primary instance
SELECT DB_NAME(database_id), differential_base_lsn
FROM sys.master_files
WHERE database_id = DB_ID('thedbname')
AND type = 0
GROUP BY DB_NAME(database_id), differential_base_lsn
Secondary instance
SELECT DB_NAME(database_id), differential_base_lsn
FROM sys.master_files
WHERE database_id = DB_ID('thedbname')
AND type = 0
GROUP BY DB_NAME(database_id), differential_base_lsn
Make a check of all backup jobs on the server to see which one is possibly taking log backups outside of the LS plan
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2012 at 10:24 am
Thanks for your responses.
We figured out the issue, it was because of the NTBackup that we setup for our VMs.
Here are more details about the issue and resolution.
More details about issue:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;951288
Solution:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;937683
October 11, 2012 at 10:40 am
How did you resume log shipping
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2012 at 2:37 pm
We stopped the SQL Writer service, stopped the log shipping backup jobs and then re-initialized the log shipping by restoring a full backup.
Other option was to find the tlog backups done outside the log shipping chain and restore them, but we decided to restore the latest full backup taken after we stopped the SQL Writer service and re-enable the log shipping backup jobs.
October 11, 2012 at 2:53 pm
Lazy DBA (10/11/2012)
We stopped the SQL Writer service, stopped the log shipping backup jobs and then re-initialized the log shipping by restoring a full backup.Other option was to find the tlog backups done outside the log shipping chain and restore them, but we decided to restore the latest full backup taken after we stopped the SQL Writer service and re-enable the log shipping backup jobs.
You don't generally need to re init from a full backup, a differential will usually work, that's why I asked you to run the queries above 😉
See my article at this link[/URL]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2012 at 3:24 pm
you are right, but we do full backups daily so re-initializing log shipping using diff was not possible because it was broken for over a day and we already had taken the full backup.
October 11, 2012 at 3:53 pm
Ok no problem
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply