RE: extremely slow log shipping restore in standby mode

Ten Centuries

Points: 1070

May 6, 2011 at 8:07 am

I've got the contents of the restorehistory records for the .trn file listed below...I tried to format it but I couldn't get a nice display to work in using this HTML format. To summarize, everything looks exactly the same between the two servers but the slow server starts at 9:56 and finishes at 19:13, the fast server starts at 18:47 and finishes at 19:48. I joined the restorehistory up with the log file based on the start and end LSN...the way the file name is offset due to the timezone caused a bit of confusion.

From the fast server log file:

2011-04-16 19:48:11.68 Backup Log was restored. Database: QAI, creation date(time): 2008/02/12(11:37:56), first LSN: 24837:114963:1, last LSN: 24922:13456:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\logshipping\qai\QAI_20110415214601.trn'}). This is an informational message. No user action is required.

From msdb..restorehistory join backupset

Slow sever:

restore_history_id1909401

restore_date4/26/11 9:56

destination_database_nameQAI

backup_set_id1909780

restore_typeL

replace0

recovery0

restart0

stop_atNULL

device_count1

stop_at_mark_nameNULL

stop_beforeNULL

backup_set_id1909780

backup_set_uuid7ECD61F5-27AA-4378-A09E-6801CBC0E6E8

media_set_id1909778

first_family_number1

first_media_number1

last_family_number1

last_media_number1

catalog_family_number1

catalog_media_number1

position1

expiration_dateNULL

software_vendor_id4608

nameNULL

descriptionNULL

software_major_version9

software_minor_version0

software_build_version4035

time_zone-16

mtf_minor_version0

first_lsn24837000011496300000

last_lsn24922000001345600000

checkpoint_lsn24903000010213400000

database_backup_lsn19295000003680200000

database_creation_date37:56.0

backup_start_date46:01.0

backup_finish_date52:08.0

typeL

sort_order52

code_page0

compatibility_level90

database_version611

backup_size5653515776

database_nameQAI

server_nameHLDSQL01

machine_nameHLDSQL01

flags512

unicode_locale1033

unicode_compare_style196609

collation_nameSQL_Latin1_General_CP1_CI_AS

is_password_protected0

recovery_modelFULL

has_bulk_logged_data0

is_snapshot0

is_readonly0

is_single_user0

has_backup_checksums0

is_damaged0

begins_log_chain0

has_incomplete_metadata0

is_force_offline0

is_copy_only0

first_recovery_fork_guidB12C1E27-CFEF-4A71-98F8-94C8DACC6994

last_recovery_fork_guidB12C1E27-CFEF-4A71-98F8-94C8DACC6994

fork_point_lsnNULL

database_guidE10AB28E-3ADB-4055-AD59-F151742F8E76

family_guidB12C1E27-CFEF-4A71-98F8-94C8DACC6994

differential_base_lsnNULL

differential_base_guidNULL

Fast Server:

restore_history_id722936

restore_date4/16/11 18:47