April 24, 2003 at 10:49 am
I am having a problem with simple log shipping that has got me stumped. My description may be a little long, but I want provide detail. I am using simple log shipping to send tran logs from my production SQL server to a non-production SQL server in order to keep a 'hot spare' database for DR use. My log shipping job failed yesterday with the error: [SQLSTATE 01000] (Message 0) Could not relay results of procedure 'sp_ApplyStandByLog' from remote server 'SERVERNAME'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed. The job successfully backed up the tran log to the other server, but this error apparently came from the restore process. I have seen this error one other time, and to resolve it, I detached/attached the DB (to get it out of Loading status) and manually restored the tran log. This time, after detaching the DB, I got the following error while trying to reattach it: Error: 9003, Severity: 20, State: 1 The LSN (1135:2153:7) passed to log scan in database 'P_MtgSrv_to_IB' is invalid. I am assuming that the tran log restore started and failed mid-restore and SQL Server did not rollback any of the restore. I guess my question is: has anyone ever seen this log shipping error before? I know that I have to recreate my hot spare, and that is not a big deal, but I would like to know what causes this error so I can prevent it in the future. I have looked on many sites and in many forums and have had no clues. Any help is greatly appreciated.
April 24, 2003 at 12:15 pm
What service pack
John Zacharkan
John Zacharkan
April 24, 2003 at 12:21 pm
April 24, 2003 at 12:51 pm
I don't see how this helps - did you see
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b275901
quote:
SQL 2000 sp 3
John Zacharkan
John Zacharkan
April 24, 2003 at 12:51 pm
quote:
I got the following error while trying to reattach it:
It seems to me your database log file was damaged. To confirm that, you could try to attach the database with 'sp_attach_single_file_db'. By the way, I can' find procedure 'sp_ApplyStandByLog'.
April 24, 2003 at 1:10 pm
Zach,
Yes, I have seen that thread, but I am already running SP3 on SQL 2000.
Allen,
sp_attach_single_file_db generates the same invalid LSN error. Also, sp_ApplyStandByLog is a custom SP. I call this SP from the Production server. It normally works great. The tran log backup that I am trying to restore is 1.8 GB. I think the last time I had this problem, the tranlog was quite large. Do you know of any size limitations for tran log restores? Syntax for sp_ApplyStanByLog is as follows:
CREATE PROCEDURE sp_ApplyStandByLog
@DBName sysname,
@BackupFileName nvarchar(120),
@UndoFile nvarchar(256)
AS
DECLARE @RestoreCmd nvarchar(510)
DECLARE @ErrDesc nvarchar(510)
SET @RestoreCmd =
'RESTORE LOG ' + @DBName
+ ' FROM DISK=''' + @BackupFileName
+ ''' WITH STANDBY=''' + @UndoFile
PRINT 'Executing ' + @RestoreCmd
RESTORE LOG @DBName FROM DISK=@BackupFileName WITH STANDBY=@UndoFile
IF @@ERROR <> 0
BEGIN
SET @ErrDesc =
'sp_ApplyStandByLog Error ' + convert(varchar(9),@@Error) + ' occurred on '
+ 'database %s restoring backup file %s'
RAISERROR (@ErrDesc,19,1,@DBName, @BackupFileName)
END
RETURN
April 25, 2003 at 12:05 pm
No answers just questions, When you back up your transaction logs do you do it with verify the integrity? Did you review the production side of this, the error log, event log, and database maint plan log? I'm guessing that the tran log backup had a hick up.
You didn't mention whether you database was still in load. Before you blow off you standby and since it sounds like your going to have to start over and restore the database from a full backup, why not try to restore with recovery on the stand by and see if it's sound and functional.
John Zacharkan
John Zacharkan
April 25, 2003 at 1:30 pm
I do not verify itegriy in my tranlog backup job. All of the errors I refferenced in my first post came from the production server. There were no messages of any sort on the standby server. Yes, my standby DB was in LOAD status. That is why I initially detached it. Is there any other way to take DB out of LOAD status w/o detaching/attaching it? I am planning on blowing away the stanby DB and recreating with a full backup from produciton DB. When you say to restore w/ recovery before blowing away standby DB, restore what? There is no backup of the standby DB and I cannot attach it to restore a tranlog.
April 28, 2003 at 6:48 am
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;272683
Evidently you can run:
RESTORE DATABASE DatabaseName WITH RECOVERY
to pull a database stuck in a load state. (Apologies, I haven't done this myself.)
Just a suggestion: you didn't mention your time interval for log shipping but if ~2 Gb log files are causing problems, you may want to reduce the time interval that the log dumps occur. This may not fix the problem but it might help with troubleshooting.
Ken
April 28, 2003 at 7:53 am
During failure testing, we have seen databases left in 'suspect' mode quite frequently when SQL Server has been stopped in the middle of a transaction log restore.
In the majority of these cases, the only solution has been to resynchronise the hot standby from the main server (not ideal when the database is 140gb).
April 29, 2003 at 9:06 am
Thanks for all of your advice. I was hoping someone has seen this same problem before and it would be a slam-dunk, but that is not the case. I will continue to monitor my standby DB and look out for any consistencies if I see this error again. Like I said, I have only seen this twice and the tranlog backups were quite large both times. Unfortunately, the DB that this backup is coming from is only updated once per day, otherwise I could set the log shipping interval to be more frequent. Thanks for all of your suggestions!!
May 1, 2003 at 11:03 am
Is it possible that someone made a change, such as adding a table or SP, to your production database using a DTS package? There is a known issue with log shipping and DTS updates of this type. DTS uses non logged transactions to transfer data objects that cause the LSN to be advanced. When this happens the next log to be applied will have the wrong starting LSN. My developers do this me once every 2-3 months. Don't know if this is your exact problem but may be something to consider.
May 1, 2003 at 12:00 pm
I have a DTS package that runs and updates data nightly, but does not alter/create objects. You said this only happens when DTS changes or creates an object?
May 1, 2003 at 12:21 pm
Yes, look at Microsoft knowledge base article Q308267. The article states that the problem has been fixed with the lastest service pack but that has not been my experience. I haven't had a chance to make sure all of the developers have applied the service pack so that may be part of my issue.
When I get this error, the only thing I can do is to take a full backup, apply it to our standby server and then restart log shipping from that point.
May 1, 2003 at 12:56 pm
I read the kbase article. I am not using the Copy SQL Server Objects, but my DTS job is using BCP, which according to the article is the component of Copy SQL Server Objects that is temporarily changing the DB's recovery model. It may be related. Thanks for the kbase!! I think this may be part of my problem. I did not install the SQL Server instance that this DB is running on, nor am I responsible for patching the server. I just checked the SP level on this instance and it is SP 2. Thanks for your help, I will recommend that they upgrade to SP 3.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply