Server A is configured as the primary source. I have a folder named R:LogShip_Source to deposit the transaction log backups and it is shared as \ServerALogShip_Source with share permissions for Everyone/Full Control.

Server B is configured as the destination and the monitor. I have a folder named C:LogShip_Destination which is where ServerB will place the TRN files after a copy and restore operation. On ServerB, I performed a standby restore of the database that I want to use for log shipping. The databases are named the same.

I ran the Database Maintenance Plan on ServerA's Enterprise Manager, which has ServerB registered as well. I configured the log backups and the copy/restore job to occur every 5 minutes. The destination node was configured using the folder listed above and I chose to use the existing database that I previously restored in standby mode. I also checked the option to have ServerB assume the primary role in the future.

The Wizard completes successfully. However, after the initial log copy, I do not see any more copy/restores done. I get Out of Sync errors after 20 minutes.

What am I missing here? I followed the step by step instructions according to Microsoft and this thing doesn't work. I noticed that I don't see a job on ServerB to perform the copy/restore operation. I see only the transaction log backup job on ServerA. Please help. Thanks.

SQLAgent must be in START state in order to perform the log shipping tasks, SQL will never attempt to stop the services until unless it is accompanied by any statement from application. So you must check the SQL error log or SQLAgent log for any reference.

It is better to enable 'AUTO_START if stopped unexpectedly'for SQLagent by all means.

Satya, the SQLAgents are running on both servers. I believe i've narrowed down the problem to the SQL Job that performs the log restore on the secondary server. This job is part of the maintenance plan and it fails everytime it executes. I run the SQL Agent under a special NT domain account. Does this domain account need full access to all databases on the secondary server?

Change the job so that it runs as the SA account. I can't really think of a reason why you'd want to it to run under another account.

You have to remember though, that once the jobs are running under the SA account, the copy job will assume the permissions of the service account for SQL Server agent so you have to make sure that the service account is a domain account with directory permissions to the source folder on the primary server. Otherwise the copy job will fail.

On another note, check out the msdb..log_shipping_plan_history table on the secondary server info for details on the error.

One detail that i forgot to add to my setup is that the source node is actually a 2 node SQL cluster, so it has a shared computer name. The other node is just a single server that I'd like to send logs to.

Do you guys think this would have anything to do with the problem I'm having?

One detail that i forgot to add to my setup is that the source node is actually a 2 node SQL cluster, so it has a shared computer name. The other node is just a single server that I'd like to send logs to.

Do you guys think this would have anything to do with the problem I'm having?

Thanks

That shouldn't make a difference.

Can you do a select * from msdb..log_shipping_history table on the secondary server and post the results of any error rows in that table?

I run the SQL Agent under a special NT domain account. Does this domain account need full access to all databases on the secondary server?
Establish security to all the servers. The Windows account you use to set up log shipping must have SQL Server systems administrator (sa) privileges on all the servers.

I ran the query on the history table and for some reason, the only TRN file that's being listed is the first_file_000000000000.trn. It looks like the copies from the primary to the secondary just aren't happening. I don't see any of the copied logs on the secondary server folder that I specified to receive the logs from the primary.

sounds like it could be one of a couple of problems. My guess is that it's either a permissions problem or the source directory is not correct.

On the seconday server, run the following query:

select * from msdb..log_shipping_plans

Find the log shipping plan and then look at the source_dir column. This should be a UNC path (e.g. "\primary_server...path")

If it is not a UNC path then you can either update the column directly and set it the correct UNC path or better still, setup log shipping from scratch. The reason I suggest setting up log shipping from scratch is that I cannot honestly remember if there are other references to the source directory in other tables.

When you set up log shipping, on the screen after you specify the transaction log backups, you will be asked to specify the primary source path - this should be a UNC path, not a local path.

If all of the above is already setup correctly and you're still getting errors then it's possibly a permissions problem.

Check the account that is running the SQL Server services on the secondary server. The services on the seconday server should be a domain account that has permissions to read from the above specified UNC path on the primary server. Typically though, it helps if both machines are running under the same domain account.

Ok, well i made more user account changes. The restore job now works, but now the backup job fails. By default the Maintenance wizard creates 5 jobs on the secondary server. It creates two restore jobs a copy alert job and a backup log alert job. The backup log alert job was successful after the first few job runs, but then started to fail. Perhaps this job needs to coordinate with the time settings and thresholds?<br /><br />This is so frustrating <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />

The backup, copy and restore jobs are all working now but the log shipping monitoring is incorrectly reporting that the backup jobs are failing? Is that right?

It sounds again like it could be a permissions problem. Basically, every time the backup job runs on the primary server it will attempt to update the log_shipping_primaries table on the secondary server.

We've gone through this already but just check that the account that is running SQL Server on both machines is the same and a sysadmin on both machines.

Also, make sure that the backup job on the primary is running as sa - this is to ensure that it runs under the security context of the service account.

If none of that works, run the following on the secondary server:

select * from msdb..log_shipping_primaries

Check the last column, source_directory and make sure that it's a UNC path pointing to the primary server's source log backup path.

As for the 4 jobs on the secondary server. Have you deleted log shipping at any time? If so, sometimes it fails to delete the corresponding log shipping jobs on the secondary server and this is perhaps why you're seeing 2 copy jobs and 2 restore jobs. That's the only thing I can think of.

If you look in the msdb..log_shipping_plans table, there's a column for the copy_job_id and a column for the load_job_id. These point to the job_id in msdb..sysjobs. I'm assuming there's only one plan in the log_shipping_plans table: Just find the 2 jobs for the corresponding copy_job_id and load_job_id and then delete (through Enterprise Manager) the two OTHER jobs.

Here you can fill in PRIMARY SERVER with the server name of the source. and DB is the name of the database participating in log shipping.

Once I fixed the security logins, I was getting the logs to finally copy over to the secondary system and it would do its restore with no problems.

The jobs all run well without failing for about 10 minutes. Then all of a sudden the Log Shipping Alert Job - Backup fails. I believe it does so after the Backup Alert threshold kicks off. It would keep reporting the error:

Hi,<br /><br />ok so we've got log shipping working it seems but we're getting alerts.<br /><br />The "Log Shipping Alert Job - Backup" job just checks the local msdb..log_shipping_primaries table and checks the last_updated column so the problem is unlikely to be with this job itself.<br /><br />Do a select * from msdb..log_shipping_primaries on the secondary server and check to see if the table is getting updated . A new record should be created in this table for every log backup that runs on the primary server. If there isn't, then you know the problem lies with the primary server and it's inability to write to this table.<br /><br />On the Primary server, make sure the backup job is running under sa. I know that it would seem that running it under the domain account should work but I remember there been some kind of problem with this. If you run it under sa then it will use whatever account the SQL Server services have been set up with.<br /><br />The two log shipping alert jobs you mention don't actually connect to the primary server so it shouldn't matter what account they are running under but for completness I would set them to run under the sa account anyway.<br /><br />Let's see if that helps. Finger crossed <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by carlitosway</i><br /><br />Karl,<br /><br />Just to clarify, after running the wizard it creates the following jobs on the secondary server:<br /><br />1. Log Shipping Alert Job - Backup<br />2. Log Shipping Alert Job - Restore<br />3. Log Shipping copy for (PRIMARY SERVER).DB_logshipping<br />4. Log Shipping restore for (PRIMARY SERVER).DB_logshipping<br /><br />Here you can fill in PRIMARY SERVER with the server name of the source. and DB is the name of the database participating in log shipping. <br /><br />Once I fixed the security logins, I was getting the logs to finally copy over to the secondary system and it would do its restore with no problems.<br /><br />The jobs all run well without failing for about 10 minutes. Then all of a sudden the Log Shipping Alert Job - Backup fails. I believe it does so after the Backup Alert threshold kicks off. It would keep reporting the error:<br /><br />Error: 14420, Severity: 16, State: 1<br />The log shipping source (SERVERNAME) has not backed up for X minutes.<br /><br />However, I do see that the logs are being copied over to the second machine.<br /><br />I'm running the first two jobs under the local SA account for the secondary server, and the other two jobs I run them under the domain account since I believe it needs to connect to the other system. <br /><br />So, that's where I'm at now. I appreciate your help<br /><br />-C<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com

If it works you will get a return value of 1. If you don't then you'll know that the service account running SQL Server on the primary server does not have access to the monitoring server with sysadmin privileges.

The only other thing you might want to check is the contents of the msdb..log_shipping_monitor table on the primary server. Make sure that the name of the monitor server is correct and that it is using NT Authentication (logon_type=1).

In fact, it probably would not hurt to re-specify the monitoring server.

If that still does not help your situation then you could try running the above command again but use a logon_type of 2 instead of 1, in which case you'll need to specify the @password parameter for the log_shipping_monitor_probe login. The above sproc is fully-documented in BOL. The log_shipping_monitor_probe login should have been created as part of log shipping (assuming you're running a mixed authentication environment). I've never really used it because I've mostly worked in NT authentication environments but I assume you might want to set its password on both the primary and secondary servers. I'm sure there's some documentation on it in msdn.

If none if this works I'm afraid I'm pretty much out of all other ideas. Let me know what you find.

I'm confused. The wizard should only create one restore job, not two - and the restore job should be on the secondary server, not the primary.

Either way, I just picked up on something you mentioned. The domain account should be a member of the sysadmin server role on both servers, which will then remove the need to explicitly specify permissions.

Sorry yes the wizard creates a restore job and a restore alert job, both on the secondary. The restore job is the one that's failing now. The domain account has the sysadmin role on both servers. Still no luck :0(