Log Shipping


What are common reasons which breaks or stops the Log Shipping?

         Recent modifications on the shared folders.

         Human error like, either someone used the option of truncate only or switched the recovery model.

         date/time for the windows servers unmatching due to any DST activities.

         Datafile added on Primary on different drives then you need to apply that on secondary with move until that your log shipping restore job will fail.

         Any I/O,Memory,N/w bottleneck.

         Your tuf file is missing.

         You may have set the incorrect value for the Out of Sync Alert threshold.

         Might be you have scheduled the jobs in same time.

         Might be your MSDB database is full.


What are your basic steps to reconfigure the Log Shipping?

         Disable all the log shipping jobs in source and destination servers.

         Take a full backup in source and restore it in secondary server using the With Standby option.

         Enable all the jobs you disabled previously in step1.



Is it possible load balance in log shipping?

Yes: its possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, there you select STANDBY option to make the secondary database readonly.


Can I take full backup of the log shipped database in secondary server?

No: You cannot take the full backup.

What are the benefits of Log Shipping?

         Log shipping doesnít require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server.

         Once log shipping has been implemented, it is relatively easy to maintain.

         Assuming you have implemented log shipping correctly, it is very reliable.

         The manual failover process is generally very short, typically 15 minutes or less.

         Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed.

         Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.


What are Disadvantage of Log Shipping? 


1. No Automatic Fail-over: DBA needs to manually fail-over the server.

2. Downtime is required when there is fail-over.

3. Possible Data Loss: Some data can lost in case of server crash, it depends on schedule of Log Shipping jobs.

4. Log shipping set-up cannot be scripted.

5. Each database which needs to be log shipped should be set up through separate Log shipping Wizard.


What are the log shipping frequent issues you have faced?

It depends. I have seen some issues like transaction log cannot restored on the secondary database which is due to that the transaction log sequence is broken.

Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible?

Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised.

Is it possible to configure Log Shipping from lower version to upper version and Vice versa?

Yes it is possible to configure Log Shipping from lower to upper version. But it is not possible vice versa.


What all jobs are created after configuring the Log Shipping?

Internally when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job

         Backup job: This job is created on Primary Server; this job takes the transaction log backup of the Database on a scheduled time

         Copy Job: This job is created on Secondary Server, this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.

         Restore Job: This job is created on Secondary Server; this job restored the copied transaction log backup on the Secondary Server.

What permissions are required for shared folders on Primary and secondary for the service accounts?

         For the backup job, read/write permissions to the backup directory are required to the following:

SQL Server service account on the primary server instance.

Proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.

         For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.

         For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.


What is TUF file?

TUF stands for Transaction Undo file.

What will happen to Log Shipping if TUF file is corrupted or lost?

The log shipping will not work. We have to setup the Log Shipping again.

If you create a Job on the Primary database server, will it automatically be created on the secondary server or not?

No, it will not be created on the secondary server.

If you create a user on the Primary database, will it automatically be created on the secondary or not?

Yes, it will be created automatically on the secondary database.

Can we take full backup of the log shipped database in primary server?

Yes itís possible. We can take full backup of log shipped database and this wonít affect the log shipping.

  How to failover secondary server, when the Primary Server fails?


If the Primary Server will become un-available, do the following steps.

a)    Take the Tail of Log from Primary server if possible.

b)    Restore Tail of log into all Secondary Database

c)     Remove Log-shipping configuration from Primary Server

d)    Select any one of Secondary server and bring into online with Alter Database DBName set Online

e)    Right click on Primary Database and Generate script for Users and Logins.

f)     Then move the script to Secondary server to create Users and Logins

g)    Re-configure log shipping from New Server (Secondary server)



What are errors occurred in Log shipping?


There are two errors are occurred during Log shipping

1)    14420:- This error occurs when the Backup job fails

2)    14421:- This error occurs when the Restoring job fails


What is WRK file in log shipping?


he .wrk files are produced when the transaction log backups are copied from the backup location to the secondary server (by the agent job on the secondary). The files are named .wrk during the copy operation; when they have been completely copied they are renamed to have the .trn extension


         TUF File: Itís Transaction Undo File. It Generated only when you Have Configured Log Shipping With Stand by Option. Since In Stand by Log Shipping Secondary Database is Available to User. So TUF Keep Pending Transaction Which are in Log File Came from Primary So That when Next Log Backup Will Come From Primary They Can Be Synchronized At Secondary.

         .WRK :  This Extension Is Given To A File Which is  Being Copied From Primary Backup Location to Secondary and Once Copy Process has been completed these file are renamed with .trn file. 


Types of mode in SQL Log shipping?


Two modes are used in log shipping:

1. No-Recovery- No user access is given to the secondary database and the database will be in Restoring state so the database does not have to care about uncommitted transactions.

2. STANDBY - secondary database can be used for Read-only purpose and .TUF file is used to contain the uncommitted transactions.