Post reply

LS_restore job is taking more than 1/2 an hour

  • July 2, 2009 at 1:29 am

    #139394

    I have configured logshipping successfully on ss2k5....yet first time LS_restore job is taking more than 1/2 an hour

    view history of LS_restore

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,0,MADANB,LSRestore_ajayg_ajay_LS,(Job outcome),,The job succeeded. The Job was invoked by Schedule 10 (DefaultRestoreJobSchedule). The last step to run was step 1 (Log shipping restore log job step.).,00:00:17,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,Executed as user: MADANB\syncaccount. The step succeeded.,00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.63----- END OF TRANSACTION LOG RESTORE -----Exit Status: 0 (Success),00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.61The restore operation was successful. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777',00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.58Deleting old log backup files. Primary Database: 'ajay_LS',00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.49Searching through log backup files for first log backup to restore. Secondary DB: 'ajay_LS'2009-07-02 12:45:16.50Could not find a log backup file that could be applied to secondary database 'ajay_LS'.2009-07-02 12:45:16.53The restore operation was successful. Secondary Database: 'ajay_LS' Number of log backup files restored: 0,00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.38Retrieved database restore settings. Secondary Database: 'ajay_LS' Restore Delay: 0 Restore All: True Restore Mode: Standby Disconnect Users: True Last Restored File: Block Size: Not Specified Buffer Count: Not Specified Max Transfer Size: Not Specified,00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.36Retrieved common restore settings. Primary Server: 'AJAYG' Primary Database: 'ajay_LS' Backup Destination Directory: 'e:\translogs' File Retention Period: 4320 minute(s),00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.33Retrieving restore settings. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777',00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.32Starting transaction log restore. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777',00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:45:16.19----- START OF TRANSACTION LOG RESTORE -----,00:00:16,0,0,,,,0

    07/02/2009 12:45:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,Microsoft (R) SQL Server Log Shipping Agent[Assembly Version = 9.0.242.0 File Version = 9.00.3042.00]c Microsoft Corp. All rights reserved.,00:00:16,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,0,MADANB,LSRestore_ajayg_ajay_LS,(Job outcome),,The job succeeded. The Job was invoked by Schedule 10 (DefaultRestoreJobSchedule). The last step to run was step 1 (Log shipping restore log job step.).,00:00:15,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,Executed as user: MADANB\syncaccount. The step succeeded.,00:00:15,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:30:15.63Searching through log backup files for first log backup to restore. Secondary DB: 'ajay_LS'2009-07-02 12:30:15.63Could not find a log backup file that could be applied to secondary database 'ajay_LS'.2009-07-02 12:30:15.64The restore operation was successful. Secondary Database: 'ajay_LS' Number of log backup files restored: 02009-07-02 12:30:15.64Deleting old log backup files. Primary Database: 'ajay_LS'2009-07-02 12:30:15.66The restore operation was successful. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777'2009-07-02 12:30:15.67----- END OF TRANSACTION LOG RESTORE -----Exit Status: 0 (Success),00:00:15,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:30:15.55Retrieved database restore settings. Secondary Database: 'ajay_LS' Restore Delay: 0 Restore All: True Restore Mode: Standby Disconnect Users: True Last Restored File: Block Size: Not Specified Buffer Count: Not Specified Max Transfer Size: Not Specified,00:00:15,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,2009-07-02 12:30:15.50Retrieving restore settings. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777'2009-07-02 12:30:15.52Retrieved common restore settings. Primary Server: 'AJAYG' Primary Database: 'ajay_LS' Backup Destination Directory: 'e:\translogs' File Retention Period: 4320 minute(s),00:00:15,0,0,,,,0

    07/02/2009 12:30:00,LSRestore_ajayg_ajay_LS,Unknown,1,MADANB,LSRestore_ajayg_ajay_LS,Log shipping restore log job step.,,Microsoft (R) SQL Server Log Shipping Agent[Assembly Version = 9.0.242.0 File Version = 9.00.3042.00]c Microsoft Corp. All rights reserved.2009-07-02 12:30:15.44----- START OF TRANSACTION LOG RESTORE -----2009-07-02 12:30:15.48Starting transaction log restore. Secondary ID: '20f91043-1f70-492c-a85b-15abb9333777',00:00:15,0,0,,,,0

    Thanks

  • July 2, 2009 at 2:45 am

    #1018977

    It is configured successfully,But the data is not reflecting on another server.

    Thanks

  • July 2, 2009 at 2:57 am

    #1018979

    Hi,

    It is quite possible and normal for this to occur. It depends on how many Transaction Log backups need to be applied to the Log Shipped Database.

    I assume you configured your log shipping deployment by restoring a database backup in order to create the log shipped database?

    If so, how many transaction log backups have been taken since the database backup was created?

    John Sansom (@sqlBrit) | www.johnsansom.com

  • July 2, 2009 at 3:03 am

    #1018980

    Dear John,

    Thanx for reply...

    1. In every 15 minutes there is a .trn file is making in shared folder.Why is it so?

    2. If I am creating a new table into primary server then its not reflecting on secondry server...will it reflect?

    Thanks

  • July 2, 2009 at 3:12 am

    #1018984

    In a very very small nutt shell, SQL Server Log Shipping is made up of three jobs, a backup, a copy and a restore.

    The Backup Job creates a Transaction Log backup of the database on the Primary server.

    The Copy Job copies the Transaction Log to the Secondary Server.

    The Restore Job, Restores(applies) the Transaction Log to the Secondary Server database.

    It sounds as though you have configured your log shipping backup job to take a Transaction Log Backup of the Primary database. The Transaction Log Backup process is both a normal and essential component of the SQL Server Log Shipping process. Provided you are happy with the 15 minute interval then all is well.

    For a complete overview of SQL Server Log Shipping please read the following Microsoft SQL Server Books Online reference.

    Once your Secondary Database, catches up with the Primary (probably up to 15 minutes behine, dependent on the schedule/timings you have set for the copy and restore jobs), any transactions that are applied at the Primary will be Log Shipped to the Secondary, including any table modifications you make.

    http://msdn.microsoft.com/en-us/library/ms187103.aspx

    Hope this helps.

    John Sansom (@sqlBrit) | www.johnsansom.com

  • July 2, 2009 at 3:23 am

    #1018987

    Dear John,

    Can u tell me?After how much time my data will be reflected to the secondry server,In the shared folder of secondry server .tuf file is being created...lot of confusions...

    Please help

    Thanks

  • July 2, 2009 at 3:44 am

    #1018998

    I cannot say for sure what your latency will be becuase it is dependent on factors specific to your environment i.e. the hardware involved, the network and of course the specifics of the Log Shipping configuration.

    The .tuf file is nothing to be concerned with and again is part of standard behaviour for a Log Shipped Database that is in Standby mode. For more details about the .tuf or Transaction Undo File have a read through the following SQL Server Central Forum post.

    http://www.sqlservercentral.com/Forums/Topic421858-5-1.aspx

    John Sansom (@sqlBrit) | www.johnsansom.com

  • July 2, 2009 at 6:29 am

    #1019080

    guptaajay1985 (7/2/2009)

    Can u tell me?After how much time my data will be reflected to the secondry server,

    The moment your transaction log is restored on the secondary server successfully, u'll be able to see the changes. Check out for your backup/copy/restore interval. you may need to decrease the time lag.


    Pradeep Singh

  • July 2, 2009 at 6:57 am

    #1019101

    Restoring with "STANDBY" is always going to be slower than "NORECOVERY" because SQL Server has to leave the database transactionally consistent in between restores (hence the .tuf file which contains the rolled back transactions).

  • July 2, 2009 at 10:26 pm

    #1019585

    Thanx...

    Yes it is working, Can u please tell how to decrease the time lag...

    Thanks

  • July 3, 2009 at 12:35 am

    #1019627

    guptaajay1985 (7/2/2009)

    Thanx...

    Yes it is working, Can u please tell how to decrease the time lag...

    check out for these jobs and increase the frequency with which they run.


    Pradeep Singh

  • July 3, 2009 at 2:22 am

    #1019658

    Hi Ajay,

    As Pradeep explains, you need to manage and tweak the schedules of the three log shipping jobs (backup/copy/restore) to your needs.

    John Sansom (@sqlBrit) | www.johnsansom.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply