0

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:

BACKUP LOG [DBName]        
TO DISK = 'Z:\DBName_TimeStamp.trn'           
WITH NAME = N'DBName_Log', INIT, FORMAT;

Restore:

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
Improve this question
6
  • 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
    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
    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
    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
    May 20, 2019 at 21:10
  • What version of SQL Server? Have you collected execution plans from both executions?
    – HandyD
    May 21, 2019 at 0:48

0

Reset to default

Your Answer

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