Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2790
  • Last Modified:

SQL Log ship fails with 14421 error after several days

I'm having a big issue with my log ship for Reports, The logs are being shipped to a second server running the copy of the database for report purposes.
for the life of me I cant figure out why the restore of logs fail after several days of running with no issues.
I set the system up after looking at a couple reasons for that 14421 error like (time, and log maintenance). these do not seem to be an issue as I removed the maintenance and the time on the 2 servers are synced to a second or 2.
I need some help resolving this issue please has anyone seen this issue before, I don't know why it would run for several days and stop on the last reset I started the process on Friday 3/18 ran with no issues till 3/22.

This is an important one for me, all you SQL 2008 experts out there please help.
0
atorex
Asked:
atorex
  • 13
  • 11
  • 2
1 Solution
 
EvilPostItCommented:
Have you tried the resolution steps noted in the below article?

http://support.microsoft.com/kb/329133
0
 
atorexAuthor Commented:
Ya, that is where I started one thing is I don't have a monitor server in this environment so that eliminates a portion of the cause.  listed on the KB.
0
 
EvilPostItCommented:
How out of sync is the log shipping?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Daniel_PLDB Expert/ArchitectCommented:
You wrote that second database is used for report purposes. When database participates in log shipping as secondary and there is any activity in the database no logs will be restored. So to be able to restore logs database cannot be used - no activity in database.
0
 
EvilPostItCommented:
Thats not entirely true. The secondary can be set to kill connections upon restore.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Indeed, but we didn't set up that log shipping :)
0
 
atorexAuthor Commented:
EvilPostIt:
How out of sync is the log shipping?  at this point its been 2 days

as to the connections to the database for reporting, it is set to read only and the time the last log was restored I dont think anyone would be connected to it but possible.
 with that in mind would the log restore not resume after the connection of the report app ends?
I would not think due to data being accessed it would get out of sync if it does then what's the point of having the log ship for reporting?
0
 
atorexAuthor Commented:
I just checked and the kill connections is not checked, so if it is not checked and the secondary database is accessed the restore will go out of sync?
I assume I can change the setting after the fact to kill connections correct?
0
 
EvilPostItCommented:
You should be able to find out when the last transaction log was restored by running the following query...

select * from msdb.dbo.restorehistory

Open in new window


It could be the case that you out of sync alert is set to 2 days and it has never actually successfully restored a transaction log.

0
 
EvilPostItCommented:
Yes this will just update the restore job on the secondary.
0
 
atorexAuthor Commented:
the logs have been restoring and the older ones were deleted as per the schedule, the last log was restored on 3/22 at 6:56 AM
0
 
EvilPostItCommented:
Any ideas from the job history of the copy or the restore job shed any light on why they were not restored. After all this is a very simple process of....

1) Backup tlog
2) Copy tlog
3) Restore tlog
0
 
atorexAuthor Commented:

this is the last entry in the history table I don't see anything different from all prior entries

1237      2011-03-22 06:51:39.700      DRSReport      DRSBACKUP\Administrator      1223      L      0      1      0      NULL      1      NULL      NULL
0
 
atorexAuthor Commented:
1) Backup tlog   -- last processed at 6:56 on 3-22
2) Copy tlog  -- still active
3) Restore tlog   -- still active
there are 50 or so logs to be restored from 3/22 to 15 minutes ago. only the restore is failing all other parts of the process are running OK.
0
 
EvilPostItCommented:
On the log shipping destination server under the sql server agent, you will have a copy and a restore job. Could you right click and have a look through the history of them. This should show any errors with copying or restoring etc. You may want to check the source too, just to ensure that it is actually backing up aswell.
0
 
EvilPostItCommented:
What is the restore failing with?
0
 
atorexAuthor Commented:
the restore is failing with 14421 error logs out of sync, here is a clip of the job history

03/24/2011 10:36:00,LSAlert_DRSBACKUP,Error,1,DRSBACKUP,LSAlert_DRSBACKUP,Log shipping alert job step.,,Executed as user: DRSBACKUP\Administrator. The log shipping secondary database DRSBACKUP.DRSReports has restore threshold of 45 minutes and is out of sync. No restore was performed for 6 minutes. Restored latency is 75 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.,00:00:00,16,14421,,,,0

03/24/2011 10:34:00,LSAlert_DRSBACKUP,Error,0,DRSBACKUP,LSAlert_DRSBACKUP,(Job outcome),,The job failed.  The Job was invoked by Schedule 36 (Log shipping alert job schedule.).  The last step to run was step 1 (Log shipping alert job step.).,00:00:00,0,0,,,,0

03/24/2011 10:34:00,LSAlert_DRSBACKUP,Error,1,DRSBACKUP,LSAlert_DRSBACKUP,Log shipping alert job step.,,Executed as user: DRSBACKUP\Administrator. The log shipping secondary database DRSBACKUP.DRSReports has restore threshold of 45 minutes and is out of sync. No restore was performed for 4 minutes. Restored latency is 75 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.,00:00:00,16,14421,,,,0

03/24/2011 10:32:00,LSAlert_DRSBACKUP,Error,0,DRSBACKUP,LSAlert_DRSBACKUP,(Job outcome),,The job failed.  The Job was invoked by Schedule 36 (Log shipping alert job schedule.).  The last step to run was step 1 (Log shipping alert job step.).,00:00:00,0,0,,,,0

03/24/2011 10:32:00,LSAlert_DRSBACKUP,Error,1,DRSBACKUP,LSAlert_DRSBACKUP,Log shipping alert job step.,,Executed as user: DRSBACKUP\Administrator. The log shipping secondary database DRSBACKUP.DRSReports has restore threshold of 45 minutes and is out of sync. No restore was performed for 2 minutes. Restored latency is 75 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.,00:00:00,16,14421,,,,0

03/24/2011 10:30:00,LSAlert_DRSBACKUP,Success,0,DRSBACKUP,LSAlert_DRSBACKUP,(Job outcome),,The job succeeded.  The Job was invoked by Schedule 36 (Log shipping alert job schedule.).  The last step to run was step 1 (Log shipping alert job step.).,00:00:00,0,0,,,,0
0
 
EvilPostItCommented:
Are you drilling into the steps to see what the specific step failure is too?
0
 
atorexAuthor Commented:
Yup, each entry has only one sub entry line no additional info from what I posted. I have one other system set up as this one is (in the lab) there are no issues I'm extremely puzzled.
0
 
EvilPostItCommented:
Ok, 2 options.

1)   Run a trace against the system and then re-run the job. Take the code that the job is executing against SQL and re-run it to see what error message you get.

2)   Find out what the next logical restore file should be and try restoring it manually using

RESTORE LOG [DBNAME] FROM DISK='[BACKUPFILE LOCATION]' WITH NORECOVERY

Open in new window


This should either work or error out and make the actual error clear.
0
 
EvilPostItCommented:
Sorry, forgot that you have yours in a readable state.

It would be...

RESTORE LOG [DBNAME] FROM DISK='[BACKUPFILE LOCATION]' WITH STANDBY='[STANDYBY FILE]'

Open in new window


Although you can switch between a NORECOVERY state and a STANDBY state easily with the above 2 restore commands.
0
 
atorexAuthor Commented:
OK, what I have done is restore a current backup and got the log ship back active and will monitor if and when the error comes back. I would think if not tomorrow Monday I will know if there is a failure again.
I do have loads of 14421 errors in the log I recall seeing somewhere that it can be caused by previous log shipping jobs not fully removed. do you agree?
so anyway it has been up for about 45 minutes and 2 logs have restored successfully.  Thanks for all the guidance so far I will update with status to morrow and Monday hope it continues to restore.
0
 
EvilPostItCommented:
Possibly. If another transaction log back had been taken of the primary database then effectivly a transaction log would have been missed. It would have just been a case of finding that transaction log. (Which you can find out from the source) and then copying it over and restoring it. Then all would just kick back in.
0
 
atorexAuthor Commented:
I have just checked and the system is up to date as of 5 minutes a go.

last_restored_date      last_restored_file      last_copied_file      last_copied_date
2011-03-25 07:15:42.403      R:\TO_BE_RESTORED\DRSData_20110325120000.trn      R:\TO_BE_RESTORED\DRSData_20110325121500.trn      2011-03-25 07:15:05.623
0
 
atorexAuthor Commented:
is there a way to change the state of my reporting database the secondary on the log ship from  read only to active where I can make a user edit for access to that database?
0
 
EvilPostItCommented:
If you were to make it active then this would break the log shipping as you would no longer be able to restore transaction logs.

Database recoverability and backups work specifically on some called Log Sequence Numbers (LSN for short). The have to match or the log will not be able to restore.

Have a read of this and let me know if you need a hand understanding anything.

http://msdn.microsoft.com/en-us/library/ms190411.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now