There appears to be a fairly significant difference after setting up transaction log shipping (in Standby/ Read-Only mode) through the SSMS wizard, which generates jobs that then call on outside .exe to handle the backup and restore vs doing the log shipping through a hand-written script:

BACKUP LOG [DBName]        
TO DISK = 'Z:\DBName_TimeStamp.trn'           


EXEC [LSServer].[master].sys.sp_executesql N'RESTORE LOG [DBName] FROM DISK = N''B:\DBName_TimeStamp.trn'' 
WITH STANDBY = N''B:\DBName.standby'';';

When utilizing the log shipping through the wizard the database seems to be ready immediately and does not experience any lag time the first time any stored procedure is executed/ query run. However, when I set up the backup and restore through my script above the first time a stored procedure is executed against the recently restored database it takes 10x or more time to complete (I originally asked about this issue here: After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow ).

I've eliminated all possibilities of different disks/ hardware etc. causing the problem as the issue is now affecting the same instance/ database that was originally using the log shipping set up by the wizard and has begun to experience the same slow initial execution/ query after restore.
The .trn file sizes look the same which ever method I use.

What can be done to identify/ eliminate this difference without going through the SSMS wizard?

Some additional information included in the linked question:

The only difference being the primary server executes with 1.4 seconds of Wait time on server replies and the secondary takes 81.3 seconds.

I do see a large number of PAGEIOLATCH_SH locks from the first execution, as you predicted:

diff after first exec vs diff after second exec
waiting_tasks_count    10903    918  
wait_time_ms          411129  12768  
  • 2
    Did you try using dbatools - for setting up logshipping as an alternative to GUI ? We dont know how your scripts are written, so its hard to tell.
    – Kin Shah
    Commented May 20, 2019 at 18:26
  • I included my backup and restore scripts in my question. BACKUP LOG WITH NAME, INIT, FORMAT. RESTORE LOG WITH STANDBY.
    – RIanGillis
    Commented May 20, 2019 at 18:45
  • Why you want to do restore using a linked server ? Is the share not shared between the logshiping servers ?
    – Kin Shah
    Commented May 20, 2019 at 20:15
  • I'm doing a staggered/ round-robin restore at 5 minute intervals to multiple instances so the data is never more than 5 minutes old and the users do not get kicked out every 5 minutes when they are attempting to generate and download reports. The primary db controls everything and tells the reporting server which instance is the most up-to-date. The share is Z:\ on the primary, B:\ on the secondary.
    – RIanGillis
    Commented May 20, 2019 at 21:10
  • What version of SQL Server? Have you collected execution plans from both executions?
    – HandyD
    Commented May 21, 2019 at 0:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.