Solved

SQL Log ship fails with 14421 error after several days

Posted on 2011-03-24
26
2,134 Views
Last Modified: 2012-08-14
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
Comment
Question by:atorex
  • 13
  • 11
  • 2
26 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35206945
Have you tried the resolution steps noted in the below article?

http://support.microsoft.com/kb/329133
0
 

Author Comment

by:atorex
ID: 35207110
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207265
How out of sync is the log shipping?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35207278
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207289
Thats not entirely true. The secondary can be set to kill connections upon restore.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35207386
Indeed, but we didn't set up that log shipping :)
0
 

Author Comment

by:atorex
ID: 35207411
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
 

Author Comment

by:atorex
ID: 35207435
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207438
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207445
Yes this will just update the restore job on the secondary.
0
 

Author Comment

by:atorex
ID: 35207620
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207749
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
 

Author Comment

by:atorex
ID: 35207831

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:atorex
ID: 35207873
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35207896
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
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 35207907
What is the restore failing with?
0
 

Author Comment

by:atorex
ID: 35208648
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35208656
Are you drilling into the steps to see what the specific step failure is too?
0
 

Author Comment

by:atorex
ID: 35208694
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35208734
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35208877
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
 

Author Comment

by:atorex
ID: 35208941
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35208975
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
 

Author Comment

by:atorex
ID: 35214444
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
 

Author Comment

by:atorex
ID: 35214516
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35214547
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Experts-Exchange users below are the steps you can follow to upgrade your Lync server to latest CU's or cumulative updates. Note: Perform it during non-production hours.   Step 1: Backup your lync and SQL server database. Follow below article: h…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now