This entry is part 1 of a 7 part series. To jump to a particular entry, you can click any of the following links:
Allrighty then. If you're following along with log shipping entries, you've now got one (or more) maintenance plans. Hopefully things are going along swimmingly, but in case they're not... here's a few errors that I ran into and how I fixed them.
I'm going to discuss the following errors:
- Error 14261: The specified plan_name ({plan_name}) already exists
- may also show "The specified @Name ({plan_name}) already exists"
- Cannot copy initialization file to secondary server
- Monitor already exists
Error 14261: The specified plan_name ({plan_name}) already exists.
This error may also display the message "The specified @Name ({plan_name}) already exists". Microsoft has documented this problem... basically the wizard does not clean up after itself correctly in certain scenarios. Here are some scenarios that may cause this error to occur:
- The database maintenance plan wizard was run unsuccessfully in the past
- A maintenance plan was deleted and recreated
- An error occurs when trying to update a log shipping destination.
The actual problem is that the wizard is not cleaning up the Log Shipping Plans table and also does not get rid of the job info that is created on the destination server. I created the following script to remove the entries from the tables. This script should be run on each destination server for your maintenance plan.
Copy the script into query analyzer and replace the value of the @Name variable with the name specified in the error message (be sure to leave the wildcard characters in there). To ensure that you see all of the messages generated by the script, make sure your results are coming out in text form. This is done in query analyzer by selecting "Query" from the menu, then selecing "Results in Text Form".
After running the script, look at the records affected. If there's more than one record affected on ay table other than the log_shipping_plan_history table then there was a problem. Immediately Rollback the Transaction! If the counts were ok, then commit the transaction.
begin transaction -- <-- Start running here! DO THIS INSIDE OF A TRAN!!!
----------------------------------------------
--Delete the log shipping plan --
----------------------------------------------
DECLARE @name varchar(1000)
DECLARE @plan_id varchar(1000)
DECLARE @job_id varchar(1000)
SET @Name = '%{Logshipping_PlanName}%' -- <-- Include wildcard characters
SELECT @plan_id = plan_id from log_shipping_plans where plan_name like @Name
print 'log_shipping_plan_databases - SHOULD NOT RETURN MORE THAN 1!!!'
delete from log_shipping_plan_databases where plan_id = @plan_id
print 'log_shipping_plan_history - may return more than 1'
delete from log_shipping_plan_history where plan_id = @plan_id
print 'log_shipping_plans - SHOULD NOT RETURN MORE THAN 1!!!'
delete from log_shipping_plans where plan_id = @plan_id
-----------------------------------------
--Delete the copy job --
-----------------------------------------
SELECT @job_id = Job_ID from sysjobs where name like '%copy%' and name like @Name
print 'sysjobs_view - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobs_view where job_id = @job_id
print 'sysjobservers - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobservers where job_id = @job_id
print 'sysjobsteps - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobsteps where job_id = @job_id
print 'sysjobschedules - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobschedules where job_id = @job_id
print 'sysjobs - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobs where job_id = @job_id
-----------------------------------------
--Delete the restore job --
-----------------------------------------
SELECT @job_id = Job_ID from sysjobs where name like '%restore%' and name like @Name
print 'sysjobs_view - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobs_view where job_id = @job_id
print 'sysjobservers - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobservers where job_id = @job_id
print 'sysjobsteps - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobsteps where job_id = @job_id
print 'sysjobschedules - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobschedules where job_id = @job_id
print 'sysjobs - SHOULD NOT RETURN MORE THAN 1!!!'
delete from sysjobs where job_id = @job_id
--commit transaction
--rollback transaction
Once you successfully run this script (and commit it) you can execute the final "create" step of your maintenance plan without receiving the error.
Cannot copy initialization file to secondary server
This error happened for me during the setup process. After backing up the database (step 1) the setup process fails on step two, giving the error 'Cannot copy initialization file to secondary server {servername}.
If you get this, start by:
- Make sure that your source and destination paths are correct
- Make sure that both Source and Destination PCs have the SQL Server and SQLAgent services running under the same account (your generic account that we created in step 3)
- Make sure that your generic account (that your agents are running under) have sufficient rights on both the source and destination directories.
If all of the above checks are valid and you're still getting the error, check the sharing on the destination server. In one instance I had the following drive structure:
- E: (data drive)
- LogShipping (This directory is shared)
- {Database1}
- {Database2}
- {Database3}
The destination for the tran files was actually the folder in {Database1}. When running the stup, logshipping failed. I stopped sharing the LogShipping directory and created shares on {Database1}, {Database2}, {Database3}. After making that change, the LogShipping setup worked correctly.
Monitor Already Exists
This error also occurs during the stup process. If you rreceive this error it is because there is an issue (password most likely) with the LogShipping Monitor on the server you selected to do the monitoring. When I got this error, I ran the following stored proc on the source server (in the msdb database):
sp_define_log_shipping_monitor {server}, {logintype}, {password}, {delete_existing}
The actual all will look something like this:
sp_define_log_shipping_monitor 'ServerA', 1, 'password', 1
This will drop and recreate your monitor account on the server you chose to do your monitoring. Be careful when doing this. If you change the password or login type you could cause problems with any existing logshipping jobs.
For details on this procedure, you can visit this microsoft site.
Well, those are all of the errors I ran into. Once I got over the first few stumbling blocks, I was able to complete my setup and we've been running pretty much flawless since.
In my next blog entry, I'll show you how I set up a report to run daily so that I can monitor my logshipping processes without checking through every single job every day.
Chris Antoniak DBA/Developer