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_id name type backup_size User_Name backup_start_date backup_finish_date
297558 NULL L 2605056 NT AUTHORITY\SYSTEM 2012-10-10 05:00:02.000 2012-10-10 05:00:02.000
297550 NULL L 1173440512 NT AUTHORITY\SYSTEM 2012-10-10 04:58:58.000 2012-10-10 04:59:09.000
297534 NULL D 39302793728 NT AUTHORITY\SYSTEM 2012-10-10 01:36:47.000 2012-10-10 01:36:54.000
297529 CRM_backup_2012_10_09_210004_7445550 D 39126608896 Domain\SQLSrvcAct 2012-10-09 21:19:06.000 2012-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.

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

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

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.