May 15, 2009 at 6:09 pm
Hi,
Iam having a strange error in the view history of the job LSAlert_EWVS1\INS1 as below
Executed as user:abc\service. The log shipping primary database EWVS1\INS1.Mydb_PROD has backup threshold of 60 minutes and has not performed a backup log operation for 1496 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed.
But, in EWVS1\INS1(Primary server), I have database called TestDB_PROD and I configured log shipping for that database. But the alert job shows different database Mydb_PROD.
I configured log shipping for the database TestDB_PROD couple of days ago. But now I deleted this database and created new database Mydb_PROD and trying to configure log shipping. But the alert job shows different database Mydb_PROD(which was configured 2 days ago and it does not exist now)
could please tell me why the alert job picking up the database that does not exist in that instance? Iam totally confused
May 16, 2009 at 11:10 am
At a guess behind the scenes the alert logic works on the DBID of the database rather than the name, and when you created mydb_prod after deleting testdb_prod it ended up with the same DBID.
probably best to remove log shipping first if going to delete a database which is logshipped
---------------------------------------------------------------------
May 16, 2009 at 10:25 pm
Thanks,
As you saying may be I deleted the database without removing the log shipping configuration. Now I restarted the service and recreated the log shipping for another database. But still the Alert job is taking the deleted database only...
So, there is no way to correct this issue?
May 17, 2009 at 5:23 am
I don't know any easy way
I think you need to look into the log_shipping_monitor_history_detail table and log_shipping_monitor_error_detail table in msdb.
there will be stored procs invoked when log shipping is removed, so I would set up a test database with logshipping, then remove log shipping and whilst doing so run profiler to capture the SQL issued, and see if you can use that as a basis for removing your rogue entry. No guarantees though.
---------------------------------------------------------------------
May 17, 2009 at 1:10 pm
I think there will be some way to resolve this. Could you please explain me whats the best way I can approach to resolve the issue
May 17, 2009 at 1:14 pm
pls see prev post, best I've got.
---------------------------------------------------------------------
May 18, 2009 at 1:42 am
I must agree. You will need to remove the info from the msdb.
May 18, 2009 at 5:59 am
klnsuddu (5/17/2009)
I think there will be some way to resolve this. Could you please explain me whats the best way I can approach to resolve the issue
Disable the logshipping, delete the jobs that you have created and re configure your log shipping again, that should be it.
July 14, 2009 at 2:09 pm
The alert job on primary server gives false alert because of the following .
We configured log shipping for a database and scripted that configuration using generate script option on the wizard. Then configured the log shipping for another database using the generated script by replacing the database name.
But we ran the whole script on primary and it created all 3 jobs on primary( backup copy and restore). And then we realized that we need to run half script on Primary and the rest is on secondary. So we disabled the log shipping for that database. But the 3 jobs were NOT deleted. So we manually deleted them and reconfigured the log shipping by running the half script on Primary and the rest on secondary. Now backup job created on primary and copy n restore are on secondary. Every thing is fine so far. Backup, copy and restore are running fine. But the Alert job is giving the error that the database is out of sync even though everything is fine!!!!
Date 7/14/2009 1:00:00 PM
Log Job History (LSAlert_ABC|XYZ)
Step ID 1
Server ABC|XYZ
Job Name LSAlert_ABC|XYZ
Step Name Log shipping alert job step.
Duration 00:00:00
Sql Severity 16
Sql Message ID 14421
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ABC\mssql. The log shipping secondary database ABC|XYZ.Mydb has restore threshold of 2 minutes and is out of sync. No restore was performed for 120 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.
It looking the time( 120 minutes) when we created for the first time i.e running the whole script on Primary. Its not taking the settings that we created for the second time. So what are the table need to cleanup in msdb to get rid of this false alert?
is this known issue???
July 14, 2009 at 11:08 pm
Is there any workaround to get rid of this false alert error??
July 15, 2009 at 12:24 pm
klnsuddu (7/14/2009)
Is there any workaround to get rid of this false alert error??
Yes error 1420 and 1421 are known error messages.
Did you look at this:
http://support.microsoft.com/kb/329133
cheers
July 15, 2009 at 1:13 pm
Yes, I went through the link and checked for all possible reasons for Troubleshooting Error Message 14421
But In my case, I had disabled the Log shipping configuration for the database, lets say Mydb and there are NO backup, copy and restore jobs. Even I deleted the database from primary and secondary...But Still I'm getting the this error in alert job on primary server?
So there is something to delete from msdb tables? please advice me..
July 15, 2009 at 1:45 pm
On your Secondary/Primary Server, run the following
DELETE FROM msdb.dbo.log_shipping_monitor_secondary
WHERE secondary_database='YOUR UNWANTED DBNAME'
DELETE FROM msdb.dbo.log_shipping_monitor_Primary
WHERE Primary_database='YOUR UNWANTED DBNAME'
July 15, 2009 at 2:46 pm
Commands executed on PRIMARY:
1.
select * FROM msdb.dbo.log_shipping_monitor_secondary
Result:
Secondary_Server Secondary_Database
SQL2\ins1 Unwanted database
2.
select * FROM msdb.dbo.log_shipping_monitor_Primary
Result:
Primary_Server Primary_Database
SQL1\ins1 list of all log shipping configured databases EXCEPT the Unwanted database
Commands executed on SECONDARY:
1.
select * FROM msdb.dbo.log_shipping_monitor_secondary
Result:
Secondary_Server Secondary_Database
SQL2\ins1 list of all log shipping configured databases EXCEPT the Unwanted database
2.
select * FROM msdb.dbo.log_shipping_monitor_Primary
Result:
No results.
Now executing the below script on PRIMARY will solve the problem?
DELETE FROM msdb.dbo.log_shipping_monitor_secondary
WHERE secondary_database='YOUR UNWANTED DBNAME'
Please advice me....
July 16, 2009 at 7:01 am
I would backup log_shipping_monitor_secondary table on the primary server and then
1. delete the entries that is related to your UNWANTED DB
2. One another thing, when you ran the whole scripts on Primary server, it would have created entries with "Secondary_server" as your primary server name. If you have entries like this, you need to clean up these entries from the log_shipping_monitor_secondary table.
Then run your alert job manually, it should be successful.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply