Archive for the ‘Log Shipping’ Category

I got a question from Interview. Run the Truncate Command in Log Shipping Primary Database What are the impact will happen in Secondary database.

Truncate Command impacts will  forward to the Secondary Database. another question raised in your mind. since Truncate command is non logged command so how it will make a impact on second Database.

Truncate Table  command will not delete the data’s row by row basic. instead of  SQL Server just deallocating a pages from a table so this operation captured by a log file. when this log backup file will restore to the secondary database the same thing will happen in secondary database.

DBCC Log (<database name>,4)


Read Full Post »

This Post about  during log shipping configuration if we shall provide same location for backup  and copy job then what are the impacts will occur in log shipping?

Log shipping will be working fine with out any impacts. Copy Job not need when we using same location for backup  and copy job in log shipping.

Backup Settings


Secondary Database Settings

 History of Copy Job



Read Full Post »

  1. Both source and target SQL Server instances of log shipping must be running under Domain User Account.
  2. Both source and target instances of log shipping must be running SQL Server 2005 Enterprise Edition, Workgroup Edition or Standard Edition with all SP and Patches should updated.
  3. Account(s) running SQL Server service on source and destination instance must have access to the network folder where you will store transaction log backup files.
  4. Computers hosting primary and standby SQL Servers must be connected to a network.
  5. SQL Server Agent service must be running on both source and destination servers. Log shipping is implemented as a collection of jobs. If SQL Server Agent service isn’t running, none of the jobs will execute.
  6. Log shipping must be setup at the database level. The database must use either full or bulk logged recovery model. Simple recovery model truncates transaction log during each checkpoint and does not support transaction log backups. Therefore databases that use simple recovery model cannot participate in log shipping. If you want to provide high availability for multiple databases on a single instance of SQL Server, you must setup log shipping separately for each database.
  7. Although not a prerequisite for log shipping configuration, it is essential to realize that in case of failover the target server becomes your primary database server. If you want to provide acceptable level of service to your user community in case of the primary server’s failure, then ensure that the standby (target) server has the same hardware configuration as the primary (source) server.

For More Details :- http://sqlserverpedia.com/wiki/Log_Shipping

Read Full Post »

Basically this .tuf file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.

So if you ask why standby mode, database recovery is done when the log is restored and this mode also creates a file with the extension .TUF (which is the transaction Undo file on the destination server). In this mode we will be able to access the databases.

Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

See the .tuf File Location

SELECT backup_destination_directory FROM dbo.log_shipping_secondary

Read Full Post »

The .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.  The temporary naming using the .wrk extension ensures that the files are not picked up by the restore job until successfully copied.

So, if the .wrk file you are seeing is there all the time it is likely to be an old copy that failed for some reason.  So for normal operation, you’ll see a .wrk file when each transaction log backup file is copied and then that will be renamed to a .trn file.

For More Details :- http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/62a6556e-6655-4d19-9112-1788cf7bbcfc

Read Full Post »